Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and stopping at the last record
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: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. -- Misanthrope |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and stopping at the last record
Befo Selection.Copy
Insert: iEnd = Range("B2").End(xlDown).Row Replace: Range("D:D").Select With: Range("D3:D" & iEnd).Select Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and stopping at the last record
<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. -- Misanthrope "merjet" wrote: Befo Selection.Copy Insert: iEnd = Range("B2").End(xlDown).Row Replace: Range("D:D").Select With: Range("D3:D" & iEnd).Select Hth, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP and stopping at the last record
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open new record with selected fields from previous record | Excel Discussion (Misc queries) | |||
VLOOKUP Reading Wrong Record | Excel Worksheet Functions | |||
Searching specific record using VLOOKUP function. | Excel Worksheet Functions | |||
stopping value(from cell) from increasing when vlookup copied down | Excel Discussion (Misc queries) | |||
Record Macro - Record custom user actions | Excel Programming |