Autofill a VLookup Formula to the last row of data

[flicky_longcartridge]

Hi,

I am having some trouble creating a macro to copy a vlookup formula from the 1st line of data (G2) all the way to the last row. I can easily create the VLOOKUP statement:

=VLOOKUP(A2,HIST!A$2:H$269,8,FALSE)

However this has 2 problems, firstly the range size will change, depending on the number of rows of data i.e. HIST!A$2:H$269 will not work if there are 300 lines of data.

After I have this issue solved, I then need to find a way of copying that formula down (Autofill) to the last row of data in column G on the page that the formula is running (called HISTFCST)

Any help would be much appreciated

 

[flicky_longcartridge]

I currently have:

Dim LR As Integer
Range("H2").FormulaR1C1 = "=VLOOKUP(A2,HIST!A$2:H$269,8,FALSE)
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("H2").AutoFill Destination:=Range("H2:H" & LR), Type:=xlFillDefault
With Range("H2:H" & LR)
End With

But this doesn't work!!

 

[Aladin Akyurek]

If you are on Excel 2003 (or beyond), try to use the Data|List|Create List option.

 

[flicky_longcartridge]

Unfortunately, Im Using Excel 2000... Don't have that option available to me!

 

[balajayan]

Hi,

You can change your code as below and try again.

Dim LR As Integer
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("H2").Formula = "=VLOOKUP(A2,HIST!A$2:H$" & LR & ",8,FALSE)
Range("H2").Copy
Range("H2:H"&LR).Pastespecial xlPasteAll
Application.CutCopyMode = False

 

[flicky_longcartridge]

Nice!

That works great can you briefly explain what that formula is doing, I'm trying to learn VBA a bit more...

Thanks for your help!

 

[balajayan]

LR = Range("B" & Rows.Count).End(xlUp).Row
Rows.count, counts number of rows in column B, though it is fixed for a version, can be used if the code is used against different versions. This would result 65536 for your case (Excel 2000). From B65536 it moves up to check which is the last row having a value and picks the row number and passes to the variable LR.

Range("H2").Formula = "=VLOOKUP(A2,HIST!A$2:H$" & LR & ",8,FALSE)

This line puts the formula in the cell H2. & symbol is for concatenate. LR is replaced with the number dynamically.

Other two lines are for copy from H2 and then to paste to the rest of the rows in the column H.

Hope it is clear.