Solved VBA to Add VLOOKUP Formula to last row

Microsoft Excel 2010 - complete product...
October 8, 2015 at 07:30:40
Specs: Windows 10

I would like to add a formula for a VLOOKUP to a column to the last row. This is the actual formula:

=VLOOKUP(C4,'Open Job Query'!B:L,9,FALSE)
Tried adding to the below formula but failing miserably ;(

Sub CopyColumns()

'Clear range on Datasheet to Update Timberline sheet
    Sheets("Datasheet to Update Timberline").Range("A4:E5000").ClearContents

Dim lastrow As Long, erow As Long, i As Long

lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lastrow

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet2.Cells(i, 8).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 1).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 4).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 2).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 2).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 3).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 3).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 4).PasteSpecial Paste:=xlPasteValues

<b>Sheets("Datasheet to Update Timberline").Range("E4" & erow + 1).Formula = "=VLOOKUP(C4:C" & erow & ","Sheets("Open Job Query").Range("B:L",9,FALSE)"</b>

Next i

Application.CutCopyMode = False

End Sub

Any help is greatly appreciated!

Thank you,

See More: VBA to Add VLOOKUP Formula to last row

Report •

October 8, 2015 at 09:34:39
I finally figured it out, sorry if I wasted anyone's time!

       With Worksheets("Datasheet to Update Timberline").Range("E4:E" & erow)
            .Formula = "=VLOOKUP(C4,'Open Job Query'!B:L,9,False)"
       End With

Thank you,

Report •

October 8, 2015 at 10:31:00
✔ Best Answer
Just a comment about your VLOOKUP()

You have it written so that it searches the ENTIRE column B
which in Excel 2010 is 1,048,576

That is a HUGE area to be searching and will take time.

Do you really need to search the entire column?
Wouldn't it be more efficient to search just those rows that are necessary?


Report •

October 8, 2015 at 10:43:25
Very good point, that may be why may macro is running slower. I will try incorporating the last row function as the data size will be constantly changing.

Thank you so much for your suggestion!!!


Report •

Related Solutions

October 8, 2015 at 10:58:09

The "entire column takes a long time" issue is not specifically dependent on the column itself but more dependent on the Used Range. If the last row of the Used Range is no farther down than the last row of data in the "entire column" being searched, then the Lookup functions in latest versions of Excel will ignore the blank cells below the last piece of data.


Whole Column Lookups and the Used Range

"If the size of the data you are looking up keeps changing/expanding its convenient to use a whole column as the table range. Excel’s Lookup functions are smart enough not to look in all the extra empty rows, but you have to be careful because they will scan down to the last row in the Used Range. So If you have excess formatting causing a large used range your whole-column Lookups will be super slow with missing data."

If you need to be concerned with the length of the data changing, then a Dynamic Named Range is probably the best option since it won't be impacted by the Used Range issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 8, 2015 at 11:06:44

Interesting article, with good info, thanks for the link.


Report •

October 8, 2015 at 11:21:49

You might want to try a Dynamic Named Range instead of recalculating the last cell every time the code runs.

To be honest, I don't know which is more efficient:

Having Excel determine the size of the Named Range dynamically then having VBA refer to the Named Range or having VBA calculate the last row each time the code runs.

My guess is that with the speed of most computers these days, the difference is negligible. On the other hand, the Dynamic Named Range might be useful for referring to the data in that column in other instances, both via VBA and within Excel itself.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 8, 2015 at 11:28:00
Thank you DerbyDad03, I believe my speed issues may be in the first part of my macro that copys and pastes the data values. I have changed the VLOOKUP range and the speed did not change at all.


Report •

Ask Question