This should work (tested)
Sub test()
Dim x, e, r As Range, t As Long, wbName As String
Const myDir = "C:\sample\Master Data\" '<--- folder path
Const fn As String = "master data jun'20.xlsx" '<--- workbook.name
Const ws As String = "Loader" '<--- worksheet name
Application.ScreenUpdating = False
wbName = "'" & myDir & "[" & Replace(fn, "'", "''") & "]" & ws & "'!": t = 1
For Each e In Array(1, 2, 12, 13, 17)
t = t + 1
Cells(5, t) = ExecuteExcel4Macro(wbName & "r16c" & e)
Next
For Each r In Range("b6", Range("b" & Rows.Count).End(xlUp))
r(, 3).FormulaArray = "=match(""" & r.Value & """&""" & r(, 2).Value & """," & _
wbName & "a1:a100000&" & wbName & "b1:b100000,0)"
x = r(, 3): r(, 3).Resize(, 3).ClearContents: t = 2
If Not IsError(x) Then
For Each e In Array("L", "M", "Q")
t = t + 1
r(, t).Formula = "=" & wbName & e & x
Next
r(, 3).Resize(, 3).Value = r(, 3).Resize(, 3).Value
End If
Next
Application.ScreenUpdating = True
End Sub
Bookmarks