VBA code for creating a VLOOKUP

Question

Hi guys, really hope you can help with my problem.

I'm looking for a VBA code that will will put a VLOOKUP formula in cell A2 and paste it all the way down to the end of the list that has been pasted into cell C2 onwards (the list spans across from column C to S, but the length of the list varies)

The problem I have is trying to get VBA to figure out the range of the list that's been pasted into C2 as it changes every day, and also getting VBA to stop copying and pasting the VLOOKUP formula when it reaches the end of the list that's in C2 rather than pasting all the way down to row 65,536 (or what ever the max is)..

The VLOOKUP formula goes something like this: =VLOOKUP(B2, [identified_range], F2, FALSE)

To summarise all I need VBA to do is to work out the [identified_range] and copy the formula to the bottom of the [identified_range].

Hope that make sense.

Many thanks for your help in advance.

Dave

Answer 1

Hi Dave

We can use column C to calculate last row, then we can build the formula string and insert it in A2. Then use FillDown to copy the formula down as required.

Sub InsertFormula()

Dim LastRow As Long

Dim MyFormula As String

LastRow = Range("C" & Rows.Count).End(xlUp).Row

MyFormula = "=Vlookup(B2,C$2:S$" & LastRow & ",F2,False)"

Range("A2").Formula = MyFormula

Range("A2:A" & LastRow).FillDown

End Sub

Answer 2

Thank you so much for your help this is perfect!