Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Aug 18, 2022
Solved

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 t...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 19, 2022

    hrh_dash 

    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
HansVogelaar's avatar
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's avatar
    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

     

      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        Aug 18, 2022

        HansVogelaar ,not sure why it still populate as #NA.. Tried removing .Formula and .Value, it still shows #NA..

         

        Tried using ws.Range("D2:D" & wsLastrow) = ws.Evaluate(....) it shows #value.. i don't think the references is wrong because when i manually input the formula into the cell, it works.

         

        Is there another way for this?

Resources