VLOOKUP and stopping at the last record

[NameDuJour]

I have generated some VBA code using macros as my seed code. Below is what I
have. My problem is that it gives me 'N/A' after the last row of data. This
is a dynamic spreadsheet in that it adds and deletes rows each time it's
published. Once published I would like to run this code to add rows and
headers along with inserting the VLOOKUP data in all the records BUT stop
after the last record. Whatever code does this I will also use to populate
the second added column but the 'Table_array' will change in the formula.

It basically looks at column B for a string value and inserts the
appropriate string value (from the 'Table_array') in the added column. I have
inserted the VLOOKUP formula in cell D2 and then 'copied-paste special' into
the remaining populated cells.

The actual VLOOKUP formula is =VLOOKUP($B2,TOPIPT,2,False)

I've tried to use the 'end' and the 'xlUp' keywords but this doesn't seem to
work unless I'm putting them in the wrong place.


Sub AddColumnsandData()

Columns("D:E").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "TOPIPT"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TOPNAME"

Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,TOPIPT,2,FALSE)"


Selection.Copy
Range("D").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

I thought that 'SkipBlanks:=False' thing would do it but evidently not.

Thanks for your time and help.

 

[merjet]

Before: Selection.Copy
Insert: iEnd = Range("B2").End(xlDown).Row

Replace: Range("D").Select
With: Range("D3" & iEnd).Select

Hth,
Merjet

 

[NameDuJour]

<dancingintheaisle> Merjet, thanks so, so much for your help. It worked
perfectly. Did a copy-paste of the new code for second column, changed a
couple of things and it worked beautifully. </dancingintheaisle>

I am not familiar with the 'iEnd' keyword. A search of Excel or VBA Editor
did not yield any help. Is there some place I can access that will help me in
discovering things like this?

Super Duper thanks again.

 

[merjet]

I am not familiar with the 'iEnd' keyword. A search of Excel or VBA Editor

did not yield any help. Is there some place I can access that will help me in
discovering things like this?

It isn't a keyword. It is just a variable, so named to describe its
purpose, finding the last used row in a column.

Merjet