Forum Discussion
Convert index match with multiple criteria into VBA
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 , 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
Yes, that looks OK.
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?
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
HansVogelaar ,attaching the files for your reference.
macro test workbook will be the file containing the macro.
The code will be under Sub vlkupfromworkingfile()
Attaching the files for your reference.
Again, appreciate the help.