Hi Experts,
I am trying to insert vlookup to return a range of cells in destination sheet when 4 criteria are met.
I have merged all the referenced criteria in source sheet in Column A for easier lookup. I tried this code but could not finish
Sub Extract_Data()
Dim sws As Worksheet, dws As Worksheet
Set dws = Sheets("PromOpti Tracker")
Set sws = Sheets("Master Data")
Dim Cri1Rng As Range
Dim Cri2Rng As Range
Dim Cri3Rng As Range
Dim Cri4Rng As Range
Dim Lstrow As Long
With dws
Set Criteria1 = dws.Range("$A$4")
Set Criteria2 = dws.Range("$M$14:$M$66")
Set Criteria3 = dws.Range("$O$14:$O$66")
Set Criteria4 = dws.Range("$P$11:$AE$11")
Set OutputRng = sws.Range("E2:H" & Cells(Rows.Count, "E2:H").End(xlUp).Row)
Set Lookuprng = sws.Range("A2:A" & Cells(Rows.Count, "A2:A").End(xlUp).Row)
dws.cells.Formula="=vlookup(Criteria1&Criteria2&Criteria3&Criteria4,sws.range("A2:H" & cells(rows.count,"A2:H").end(xlup).row),outputRng,0"
End Sub
Bookmarks