Vlookup Dynamic

[Shailender]

Hey All, Here is the question on vlookup, we need a vba code to fill down Vlookup dynamically till the data it is available. With each month a new column is being added in between the existing columns. In the new column inserted, vlookup function should run with Dynamic autofill. (Here is the Example which i included in the sheet)

 

[Villalobos]

Hi,

Is it help for you? I used INDEX-MATCH instead of VLOOKUP because faster and more flexible.

Option Explicit
Sub FindValue()
Dim Lastrow As Long
Dim SourceSheet As Worksheet
Dim SourceLastrow As Long

    Set SourceSheet = Worksheets("Data")
  
    With SourceSheet
        SourceLastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    With Worksheets("Vlook UP")
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("B1:B" & Lastrow).FormulaR1C1 = "=INDEX(Data!R2C5:R" & SourceLastrow & "C5,MATCH(RC[-1],Data!R2C1:R" & SourceLastrow & "C1,0))"
    End With
  
End Sub

 

[Shailender]

Hello Villalobos, thank you for getting back to my question. But still i am unable to do the vlookup dynamically. As i said i need to insert a column every month in between the columns i need to do vlookup till the last row. As i Can see above the range("B1:B" & lastrow) but i cannot give the specific range all the time when i insert the column.i need the vlookup function whenever i insert the new column in between the existing column.

It would be appreciated if someone get back to me with the solution.

Thank you in advance.

 

[shrivallabha]

Like this?

Public Sub FillFormula()
With Sheets("Vlook UP").Range("B1:B" & Sheets("Vlook UP").Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=VLOOKUP(A1,Data!$A$1:$E$" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row & ",5,0)"
End With
End Sub

In your attached sheet data start from row 1 in your Vlook UP sheet so I have used B1 and A1. Please adjust those references and sheet names in your file if different.

 

[ANKUSHRS1]

Sub test()
Dim c As Long
Dim i As Long

c = Application.WorksheetFunction.CountA(Columns("a:a"))

For i = 1 To c

Range("b" & i).Value = "=VLOOKUP(A" & i & ",Data!A:F,5,0)"

Next

End Sub