• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup by Column Header -

Vijayarc

Member
Hi Ninja Team
i am using below code to vlookup on other open excel file, now i facing issue is, every month one new column is adding mid on open file, so lookup reference column number is pointing to next column,

!!! please help me to vlookup on column header instead of ref number

Code:
Sub Vlookup_GlobelLDD()
Dim filename As String
Dim Wb1 As Workbook, wb2 As Workbook, wbA As Workbook
Dim rngToCopy As Range
Lrow = Range("B" & Rows.Count).End(xlUp).Row

For Each wbA In Application.Workbooks
    If Left(wbA.Name, 29) = "Global LDD Population Details" Then
            Set Wb1 = wbA
            Exit For
        End If
    Next

If Not Wb1 Is Nothing Then '<~~ check if you actually found the needed workbook
  Set wb2 = ThisWorkbook
    With Wb1.Sheets(1)
        .Range("DO1").FormulaR1C1 = _
        "=MID(CELL(""filename"",RC[-118]),FIND(""["",CELL(""filename"",RC[-118]))+1,FIND(""]"", CELL(""filename"",RC[-118]))-FIND(""["",CELL(""filename"",RC[-118]))-1)"
    End With
     
Wb1.Sheets(1).Range("DO1").Copy ' formula to know currect workbook name- ( to know vlookup file path)
wb2.Sheets("Sample").Range("A1").PasteSpecial xlPasteValues
   
Filepath = wb2.Sheets("Sample").Range("A1") ' save vlookup file path with above formula
Sheets("Master").Activate
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A1:AV1").AutoFilter Field:=1, Criteria1:="="  ' filter only blanks date
'----------------vlooplkup formula starts
wb2.Sheets("Master").Range("C2:C" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C2,2,0)"
wb2.Sheets("Master").Range("D2:D" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-2],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C3,3,0)"
wb2.Sheets("Master").Range("E2:E" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-3],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C4,4,0)"
wb2.Sheets("Master").Range("F2:F" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-4],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C17,17,0)"
wb2.Sheets("Master").Range("G2:G" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-5],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C38,38,0)"
wb2.Sheets("Master").Range("H2:H" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-6],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C40,40,0)"
wb2.Sheets("Master").Range("I2:I" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-7],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C99,99,0)"
wb2.Sheets("Master").Range("AX2:AX" & Lrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-48],'[" & Filepath & "]APAC LDD Renewals 2020'!C1:C105,105,0)"

End If
 
Hi Ninja

i am doing daily more than 30 vlookup on below sample "Global LDD" file and other many files too. to my below "APAC " file,
my problem on my macro is, i am using column ref number to vlookup, however on my sheets new columns will add in mid on every monthly basis
so lookup reference column number is pointing to next column and result gets wrong,
i need to change the ref number every month for 30 lookup formulas

!!! note Please find my attached sample file and data just for info, my original files have more dynamic rows and columns (huge size).

please help me for macro to vlookup on column header instead of ref number on all open workbooks.

Thanks in Advance
 

Attachments

  • APAC_Macro.xlsb
    23 KB · Views: 9
  • Global LDD Population Details 2020-09-18.xlsx
    8.7 KB · Views: 8
Hi Ninjas

please help me for macro to vlookup on column header instead of column ref number on all open workbooks.
 
If you are looking up value V in a table called Table1 and you want to return the value found in the table column called H then you can use

=VLOOKUP(V,Table1,MATCH("H",Table1[#Headers] ),0)
 
Try this instead of yours. It will place formulae in three columns with one line:
Code:
Sub Vlookup_GlobelLDD2()
Dim WorkbookFound As Boolean, wbA, LookupAddr As String, HdrsAddr As String

For Each wbA In Application.Workbooks
  If Left(wbA.Name, 29) = "Global LDD Population Details" Then
    LookupAddr = wbA.Sheets("APAC LDD Renewals 2020").Range("A:AV").Address(, , xlR1C1, external:=True)
    HdrsAddr = wbA.Sheets("APAC LDD Renewals 2020").Range("A1:AV1").Address(, , xlR1C1, external:=True)
    WorkbookFound = True
    Exit For
  End If
Next

If WorkbookFound Then    '<~~ check if you actually found the needed workbook
  With ThisWorkbook.Sheets("Master")
   ' .Range("A1:AV1").AutoFilter Field:=1, Criteria1:="="  ' filter only banks date
    .Range("C2:D" & .Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC2," & LookupAddr & ",MATCH(R1C," & HdrsAddr & ",0),0)"
  End With
End If
End Sub
It uses the header on row 1 of the Master sheet above each formula, to match the same header on the source sheet's row 1.
Currently column C shows correct results, Column D is empty on the source sheet, and column E has a different name. It's not difficult temporarily to change the headers in the Master sheet to get the wanted columns on the source sheet, then change them back. It's also not difficult to place the formulae in non-contiguous columns on the Master sheet in the same macro line.
 
Last edited:
Back
Top