Convert index match with multiple criteria into VBA

I would like to translate the index match formula below into a macro. The excel formula works perfectly but when i code it using a for loop but not sure why the values are populated as #NA from C2 to last row..

Excel Formula :

=INDEX('[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$D$2:$D$5000,MATCH(1,(C2='[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$C$2:$C$5000)*(P2='[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$P$2:$P$5000),0))

VBA code as follows:

Sub vlkupfromworkingfile()

Dim ws As Worksheet
Dim wsLastrow       As Long
Dim i               As Long

Set ws = Sheet1

ws.Range("D:D").EntireColumn.Insert
ws.Range("D1").Value = "Remarks"

wsLastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 2 To wsLastrow
    
    On Error Resume Next
    
    With ws.Range("D" & i)
        .Formula = "='C:\Users\hrhquek\Desktop\[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$D$2:$D$5000,MATCH(1,(C2='C:\Users\hrhquek\Desktop\[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$C$2:$C$5000)*(P2='C:\Users\hrhquek\Desktop\[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!$P$2:$P$5000),0))"
        .Value = .Value
    End With
    
Next i

End Sub

appreciate the assistance in advance!

excel

Formulas and Functions

Try this version:

Sub vlkupfromworkingfile()
    Dim ws As Worksheet
    Dim wsLastrow As Long
    Dim r As Long
    Const sSheet As String = "'C:\Users\hrhquek\Desktop\[working file.xlsx]Sheet1'!"

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set ws = Sheet1
    ws.Range("D:D").EntireColumn.Insert
    ws.Range("D1").Value = "Remarks"
    wsLastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    'On Error Resume Next
    For r = 2 To wsLastrow
        ws.Range("D" & r).Value = Evaluate("= Index(" & sSheet & "$D$2:$D$5000,MATCH(1,(" & _
            sSheet & "$C$2:$C$5000=C" & r & ")*(" & sSheet & "$P$2:$P$5000=P" & r & "),0))")
    Next r
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

13 Replies

HansVogelaar

MVP

Aug 18, 2022

hrh_dash

There is no need to loop. The following is much more efficient:

Sub vlkupfromworkingfile()
    Dim ws        As Worksheet
    Dim wsLastrow As Long
    Const sSheet  As String = "'C:\Users\hrhquek\Desktop\[640910 690606641710_Jan to Jul''22_working.xlsx]Sheet1'!"

    Set ws = Sheet1
    ws.Range("D:D").EntireColumn.Insert
    ws.Range("D1").Value = "Remarks"
    wsLastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
    With ws.Range("D2:D" & wsLastrow)
        .Formula = "=" & sSheet & "$D$2:$D$5000,MATCH(1,(C2=" & sSheet & "$C$2:$C$5000)*(P2=" & sSheet & "$P$2:$P$5000),0))"
        .Value = .Value
    End With
End Sub

hrh_dash
Iron Contributor
Aug 18, 2022
HansVogelaar , i just realized that i have omitted an index in the macro.

would it be correct if i were to input the index as follows?
With ws.Range("D2:D" & wsLastrow) .Formula = "= Index( " & sSheet & "$D$2:$D$5000,MATCH(1,(C2=" & sSheet & "$C$2:$C$5000)*(P2=" & sSheet & "$P$2:$P$5000),0))" .Value = .Value End With
- HansVogelaar
  MVP
  Aug 18, 2022
  hrh_dash
  
  Yes, that looks OK.

Forum Discussion

Convert index match with multiple criteria into VBA

13 Replies

Resources