Building Custom Function with Array Loop

[MojoJojo2023]

Hi all!

I am attempting to build a custom function to pass values to an array and return a value based on whether criteria matches the passed values. I am not new to VBA, but am trying to learn utilize arrays to process more in memory. Any feedback on the issue with the below would be appreciated. Thanks in advance! :

Function NP(term As Integer, dte As Date, rng As Range) As Double

Dim tmparray As Variant
Dim i As Long, j As Long

tmparray = rng

For i = LBound(tmparray, 1) To UBound(tmparray, 1)
    For j = LBound(tmparray, 1) To UBound(tmparray, 15)
        If tmparray(i, 1).Value = dte And tmparray(i, 3).Value = "Yes" And tmparray(i, 7).Value = term Then
        NP = tmparray(i, 12)
        End If
    Next j
    
Next i

End Function

 

[FormR]

Hi, you didn't really explain what the actual issue was, but it's not clear what you are trying to do with the J for loop and you don't use .value to reference the contents of the array items.

Maybe give this a go.

Function NP(term As Integer, dte As Date, rng As Range) As Double

Dim tmparray As Variant

Dim i As Long
tmparray = rng

For i = LBound(tmparray, 1) To UBound(tmparray, 1)
    If tmparray(i, 1) = dte And tmparray(i, 3) = "Yes" And tmparray(i, 7) = term Then
        NP = tmparray(i, 12)
    End If
Next i

End Function

 

[MojoJojo2023]

Sorry for the lack of clarity - I am trying to create something reminiscent of a binary search Xlookup function over a two-dimensional array. The column count will always have an upper bound of 15, but the rows may be variable depending on data used at runtime.

 

[FormR]

Hi, did you try the suggestion?

something reminiscent of a binary search

You might want to look up what a binary search is, it isn't this