Microsoft Excel 2010 - complete product...

Hi, 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 Sheet1.Columns.AutoFit Range("A1").Select End SubAny help is greatly appreciated!

Thank you,

Sandi

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 WithThank you,

Sandi

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,576That 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?MIKE

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

Sandi

Mike, 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.

From https://fastexcel.wordpress.com/201...

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.

SandiS: 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.

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

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History