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

 

[Skybluekid]

Firstly, try

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

as there is no argument for the match type in the VLOOKUP

If you are looking for Text, it will always look for an exact match. Are you looking for the word "Date" on the other sheets?

 

[Alex Blakenburg]

You are using Match to work out the Column position, as such the Match Formula needs to be looking in a "single" row being the row with the headings in it.
The below assumes your row headings are in row 1, modify it if that is not the case.
Also your fill down is based on the ActiveCell but filling down column A. That is only going to work if the Activecell is in column A

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

 

[HGDantes]

Firstly, try

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

as there is no argument for the match type in the VLOOKUP

If you are looking for Text, it will always look for an exact match. Are you looking for the word "Date" on the other sheets?

I'm actually looking to find the Header called "Date" and get data from that column. I have about 70 Vlookups on my Script and each time I'm adding or removing a column, I must go over the entire script and modify all VLookup Indexes. So I'm trying to find a better solution to match data.
Basically, I have 3 Sheets to fill in my report, and Getting data From 7 other reports, all from different sources.

 

[HGDantes]

You are using Match to work out the Column position, as such the Match Formula needs to be looking in a "single" row being the row with the headings in it.
The below assumes your row headings are in row 1, modify it if that is not the case.
Also your fill down is based on the ActiveCell but filling down column A. That is only going to work if the Activecell is in column A

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

Thanks, I will try this

 

[HGDantes]

Thanks, I will try this

It didn't worked as expexted.

I'm actually looking to find the Header called "Date" in the Sheet "Previous_OnPrem" and get data from that column. I have about 70 Vlookups on my Script and each time I'm adding or removing a column, I must go over the entire script and modify all VLookup Indexes. So I'm trying to find a better solution to match data.
Basically, I have 3 Sheets to fill in my report, and Getting data From 7 other reports, all from different sources.
One other issue is that names From Sources Files aren't always exactly the same as on my reports.

 

[Alex Blakenburg]

Does the row number for the heading row vary ?.
If it does perhaps use find to get the column & row and use the address of the Find in your Vlookup

 

[HGDantes]

Does the row number for the heading row vary ?.
If it does perhaps use find to get the column & row and use the address of the Find in your Vlookup

Yup that's the Problem, It may vary. That's why I was looking for a solution because everytime a column change, I have to review my entire script and re-index all my Vlookups.

But I think I found the Best solution, I just tried it and it seems to work..

    Dim UpdateDateSource As Long
    Sheets(Previous_OnPrem).Select
    UpdateDateSource = WorksheetFunction.Match("Date", Rows("1:1"), 0)
    Sheets("OnPrem").Select
    Range ("A2").Select
    ActiveCell.Value = "=IFERROR(VLOOKUP(" & "G2" & ",Previous_OnPrem!A:J,UpdateDateSource,0),"""")"
    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":" & "A" & RowCount)

 

[Alex Blakenburg]

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)

 

[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)

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