I have the below code that creates an array from a table in excel and then searches that array based on multiple criteria from the inputs.
Function output_string(id As Long, dt As Date, descr As String)
'set variables
Dim myarray As Variant
Dim ws As Worksheet
Dim col_count As Integer
Dim row_count As Long
Dim source_range As Range
'set worksheet
Set ws = Worksheets("Data Store")
dt = DateValue(app_dt)
'set up column and row counts for populated range
col_count = ws.Range("A2").End(xlToRight).Column
row_count = ws.Range("A2").End(xlDown).Row
'set range for populated cell space
Set source_range = ws.Range(Cells(2, 1), Cells(row_count, col_count))
'create the dimensions of the array that will store the table
ReDim myarray(1 To source_range.Rows.Count, 1 To source_range.Columns.Count)
'load data from excel table into array
myarray = source_range.Value
'get row with matching criteria
For i = LBound(myarray) To UBound(myarray)
If myarray(i, 1) = id And dt >= myarray(i, 2) And dt <= myarray(i, 4) _
And descr = myarray(i, 5) Then
output_string = myarray(i, 3)
Exit For
End If
Next i
End Function
The function gives the correct output. The problem is that the function only works on the Data Store worksheet. All the other worksheets return the #VALUE! error when I put in the exact same input. The arguments to the function can be cell references from other worksheets without erroring out.
Also, every once and a while (seemingly at random) the working function on the Data Source tab will convert from a correct value to #VALUE! as well.
I have the function in a regular module in the same workbook that I'm using. The module also has a sub program in it.
I'm really stumped on this one. Has anyone seen this before/do you know a solution?
