Vlookup with Match

[HGDantes]

Hello

I'm trying to modify these line of Script :

    Sheets("OnPrem").Select
    Range ("A2").Select
    ActiveCell.Value = "=IFERROR(VLOOKUP(" & "G2" & ",Previous_OnPrem!A:J,2,0),"""")"
    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":" & "A" & RowCount)

But I would Like to Match with a column name "Previous_OnPrem" Sheet.

I've the following, but it's not working tried :

ActiveCell.Value = "=IFERROR(VLOOKUP(" & "G2" & ",Previous_OnPrem!A:J,MATCH("Date",Previous_OnPrem!A:J,0)),"""")"
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":" & "A" & RowCount)

"Date" Being the column name on the "Previous_OnPrem" sheet.

Also, I'm not sure if the name on column Must be the exact same on both sheets, But I have several column to match in several sheets, and Column names aren't always the same.

Thanks

 

[Alex Blakenburg]

If you don't need the actual formulas in the spreadsheet just the results of the Lookups it is likely that using a dictionary will be much faster.
You might also want to look into using Power Query pulling in data from various sources and cleaning it up is its main focus.

 

[HGDantes]

Oh Sorry, I read too fast I guess. No The Row for Headers is always Row 1

Thanks. I'll try this solution as well. Not sure which one will be the fastest to run. With Over 45 000 lines and about 70 Vlookups, it may takes 15-20 minutes to run, so if I can save even a minute or 2 That would be nice.. haha

Thanks for you help

If you don't need the actual formulas in the spreadsheet just the results of the Lookups it is likely that using a dictionary will be much faster.
You might also want to look into using Power Query pulling in data from various sources and cleaning it up is its main focus.

Oh! This is really interesting. I indeed just need the data, not the formula. I even remove formula at the end of my Script :

Cells.Select
    Selection.Copy
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I will definitively read on Dictionaries and Power Queries.

Thanks

 

[HGDantes]

I asked if the Row number for heading varies, you responded with Yup but then talk about the Column changing.

If your latest post works for you then great but it is no different to the formula I gave you in that it hard codes the row as Row 1
The only difference is that since you are hardcoding A:J in you Vlookup anyway I have restricted the Row 1 search to A:J as well.
Something like this would hard code the column into the actual formula but would find the column to use, regardless of which row the heading is on.

Dim prevSht As Worksheet
Dim foundCell As Range
Dim foundCol As Long
Dim findStr As String
Dim findRng As Range

Set prevSht = Worksheets("Previous_OnPrem")
findStr = "Date"
Set findRng = prevSht.Range("A:J")

    Set foundCell = findRng.Find(What:=findStr, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
      
    ActiveCell.Value = "=IFERROR(VLOOKUP(" & Range("G2").Address(0, 0) & ",Previous_OnPrem!A:J, " & foundCell.Column & "),"""")"
    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":" & "A" & RowCount)

Thanks a lot. After testing further, my solution wasn't working properly after all, but yours did perfectly. One last question tho. Would you know how to prevent Blank cells returning "0" ? I Would Like Blank cells to be blank on my results.

Thanks

 

[Alex Blakenburg]

Thanks a lot. After testing further, my solution wasn't working properly after all, but yours did perfectly. One last question tho. Would you know how to prevent Blank cells returning "0" ? I Would Like Blank cells to be blank on my results.

Thanks

If you know the data are you returning is going to be text (not a number which includes dates) then you can simply add an empty string to the return value ie

ActiveCell.Value = "=IFERROR(VLOOKUP(" & Range("G2").Address(0, 0) & ",Previous_OnPrem!A:J, MATCH(""Date"",Previous_OnPrem!A$1:J$1,0),0) & """","""")"

That won't work if you returning numbers since it will convert the number to text which is not what you want.
In that case you will need a double lookup to test for a blank and that will really slow it down.