Offset VLOOKUP data by one row - example in message
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] |
Offset VLOOKUP data by one row - example in message
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)))" S Himmelrich wrote: 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 |
All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com