Vlookup only until the cell on the left is filled

Hi,

I have to say if there is ranking in macro knowledge going from 1 the best to 10000 the worst, I am somewhere below 9500. What I can do is to record a macro and use it. Now I face a problem which goes beyond my knowledge and I could find any help by searching on the internet because I don't even know how to search for it. You will most probably find it an easy question, sorry for describing it a bit long.

I need to prepare a list of customers each week with several data on it. Our belowed SAP system can't provide the name (column C), location (column D) and profit center (column E) for the customer number (column B), so I need to vlookup these from another excel file. My problem is that the length of the list changes from week to week. When it is longer than the one where I recorded the macro the cells are not filled with the required data after the last cell of the original list. I also would like to replace the vlookup formulas from the cells to get only numbers, but as you can see it is valid now only for cells C2:E577, because of the original recording.

Could you please help me how to solve this that all necessary cells will be filled? And many thanks in advance! This is the macro:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CUSTOMER LIST STP 2012.xls]Tabelle1'!C1:C4,4,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[CUSTOMER LIST STP 2012.xls]Tabelle1'!C1:C6,6,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[CUSTOMER LIST STP 2012.xls]Tabelle1'!C1:C8,8,0)"
Range("C2:E2").Select
Selection.AutoFill Destination:=Range("C2:E577")
Range("C2:E577").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Hello,

do these Vlookups actually return something other than an error message? The lookup table consists of only one column, but the value to be returned is in column 4, 6, or 8 respectively. That doesn't make much sense.

With regards to filling down to the last row, you can use code like this:

Please Login or Register  to view this content.