Offset VLOOKUP data by one row - example in message

[S Himmelrich]

I'm currently using the following code:

Dim lastRow9 As Long
lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count,
"B").End(xlUp).Row
Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl,
3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))"
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9)

Current Results
Results:
Column A Column B
Armstrong Jason Brenner
Armstrong Jack Kimball
Armstrong Sue Mitchell
Armstrong Lola Lumbar
Armstrong Genis Pinkerton

Kelly Jill Adler
Kelly Kim Gonzales
Kelly Lilly Lopez
[and so forth]

WHAT I'M LOOKING FOR:

Column A Column B
Armstrong Jason Brenner
Armstrong Jack Kimball
Armstrong Sue Mitchell
Armstrong Lola Lumbar
Armstrong Genis Pinkerton
Armstrong
Kelly Jill Adler
Kelly Kim Gonzales
Kelly Lilly Lopez
Kelly
[and so forth]

 

[Dave Peterson]

First, your formula is really in R1C1 reference style. You should use
..formulaR1C1 (even though excel/VBA can be forgiving).

And you can fill the entire range with that formula in one fell swoop:

Dim lastRow9 As Long
lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row
Range("a2:a" & lastRow9).FormulaR1C1 _
= "=if(rc[1]="""","""",IF(ISNA(VLOOKUP(RC[1],tbl,3,FALSE))," _
& """"",VLOOKUP(RC[1],tbl,3,FALSE)))"

I'm currently using the following code:

Dim lastRow9 As Long
lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count,
"B").End(xlUp).Row
Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl,
3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))"
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9)

Current Results
Results:
Column A Column B
Armstrong Jason Brenner
Armstrong Jack Kimball
Armstrong Sue Mitchell
Armstrong Lola Lumbar
Armstrong Genis Pinkerton

Kelly Jill Adler
Kelly Kim Gonzales
Kelly Lilly Lopez
[and so forth]

WHAT I'M LOOKING FOR:

Column A Column B
Armstrong Jason Brenner
Armstrong Jack Kimball
Armstrong Sue Mitchell
Armstrong Lola Lumbar
Armstrong Genis Pinkerton
Armstrong
Kelly Jill Adler
Kelly Kim Gonzales
Kelly Lilly Lopez
Kelly
[and so forth]