Not doing VLOOKUP on empty cells

Belair58

Board Regular
Joined
Mar 31, 2005
Messages
95
Hi,

I'm working with data that the quantity varies from month to month. Due to Excel's issues with leading zeros I've had to cut and paste data into a sheet. Once that's done I'm doing a VLOOKUP on the data. Since I never know how many cells will have data, I've created a range of 20000 cells to do the VLOOKUP on.

Here's my code for that:
Code:
Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'transmitted pricing.xls'!C14,1,FALSE)"
    Selection.Copy
    Range("E3:E20000").Select
    ActiveSheet.Paste
What I'd like to be able to do is find a way for it to only copy the VLOOKUP code down to the last needed cell. So, if A11000 is blank, don't copy the code down any farther.
The reason for this, is I believe that it's taking much longer to run the code than it needs to, because of the extra VLOOKUP on the blank cells.

Any help would be greatly appreciated.

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Code:
Sub test()
Dim End_Row As Long

End_Row = Range("A65536").End(xlUp).Row

Range("E2").FormulaR1C1 = _ 
        "=VLOOKUP(RC[-4],'transmitted pricing.xls'!C14,1,FALSE)" 
    Range("E2").Copy Destination:=Range("E3:E" & End_Row)
Application.Cutcopymode = False
End Sub
 

Forum statistics

Threads
1,136,990
Messages
5,678,979
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top