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