Index and match VBA

[sysuserwindows]

I have the table below, but when using the code VBA to search for a value as in below, it appears No match found

Planning.xlsm
	A	B	C
1	REF	Mat	QTY
2	310002	6002503950	1500
3	910077B	6002503915	3000
4	910022	6002503922	16000
5	910077	6002503931	1325000
6	910099	6002503900	2000
7	910055	6002503900	50000
8	20000000009955	6002503920	1500
9	20000000001970	6002503930	200
10	20000000008520	6002503555	1000
Sheet2

Function FindQTY(REF As Variant, Mat As String) As Long
    Dim ws As Worksheet

    Dim result As Variant
   
    ' Set the worksheet object
    Set ws = ThisWorkbook.Worksheets("SumShet")
   
   
    ' Evaluate the formula and return the result
    result = Application.Evaluate("INDEX('" & ws.Name & "'!C:C, MATCH(1, ('" & ws.Name & "'!A:A=""" & REF & """)*('" & ws.Name & "'!B:B=" & Mat & "), 0))")   
   
    ' Check if the result is an error
    If IsError(result) Then
       MsgBox "No match found"
    Else

        FindQTY = result
    End If
   
End Function

Sub callFindQTY()
    Dim arg1 As Variant
    Dim arg2 As String
   
    arg1 = "910022"
    arg2 = "6002503922"
    Debug.Print FindQTY(arg1, arg2)
End Sub

 

[James006]

Hi,
Following Evaluate seems to function as expected

Function FindQTY(arg1, arg2) As Double
Dim result As Variant
    ' Evaluate the formula and return the result
    result = Evaluate("=INDEX(C1:C10,MATCH(" & arg1 & " & " & arg2 & ",A1:A10&B1:B10,0))")
    ' Check if the result is an error
    If IsError(result) Then
       MsgBox "No match found"
    Else
        FindQTY = result
    End If
End Function

Sub callFindQTY()
Dim arg1, arg2
    arg1 = "910022"
    arg2 = "6002503922"
    Debug.Print FindQTY(arg1, arg2)
End Sub