Index Match in VBA function

[SonnyB25]

Hi,

I'm trying to develop a Index/Match function in VBA with multiple criteria (using the "&" as in an excel function) - but it doesn't work.

Does anybody know what I'm doing wrong?



Function prove(X As Range, Y As Range, Z As Range)


A = Application.WorksheetFunction.Index(X, Application.WorksheetFunction.Match("Pre-SS" & "PS", Y & Z, 0), 1)


prove = A


End Function

Thanks in advance!
Sonny

 

[steve the fish]

Does this do what you want?

Dim arr, arr1, arr2, i As Long, myVal As Variant

arr = X: arr1 = Y: arr2 = Z

For i = LBound(arr1, 1) To UBound(arr1, 1)
    If LCase(arr1(i, 1)) = "pre-ss" Then
        If LCase(arr2(i, 1)) = "ps" Then
            myVal = arr(i, 1)
            Exit For
        End If
    End If
Next
       
If Not IsEmpty(myVal) Then
    prove = myVal
Else
    prove = CVErr(xlErrNA)
End If

 

[SonnyB25]

Not quite. I need it to be a function based on the "application.worksheetfunction.xxx".

The function works in excel (within VBA), I just want to replicate it to a VBA function.

 

[Norie]

Sonny

You can't use something like Y & Z to combine 2 ranges in VBA.

 

[Marcelo Branco]

Maybe something like this...

Function prove(X As Range, Y As Range, Z As Range)
    Dim A As Variant
    
    A = Evaluate("=INDEX(" & X.Address & ", Match(""Pre-SS""&""PS""," & Y.Address & "&" & Z.Address & ", 0))")
    prove = A
End Function

M.