Forum Discussion
Solved
Absolute Referencing in a VLookup in VBA
Hello! I am trying to write a macro that does a vlookup into a range of cells from another sheet in my workbook , but I do not know how to make that range of cells constant when I apply the vlook...
OK, you can use
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], 'To Format'!R2C1:R" & lastRow & "C20, 10, FALSE)"
Is there a way to incorporate lastRow so that the array from To Format dynamically changes in size? And what does R2C1 and R1000C20 indicate?
lastRow refers to the active sheet, while the range is on the 'To Format' sheet.
R2C1 is the R1C1 way of referring to the cell in row 2 column 1, i.e. A2 in A1 notation.
R1000C20 is the R1C1 way of referring to the cell in row 1000 column 20, i,e, T1000 in A1 notation.
- I guess something I failed to mention was that even though lastRow refers to the active sheet, it was copied and pasted from To Format - thus they have the same row size. My issue predicament with R1000 is that one data set that I perform this macro on can have less than 1000 rows, but another data set can have more than 1000 rows, so I need the R# to be dynamic, hence why I want to use lastRow. Isn't setting R1000 hard coding the range and making it not dynamic?
OK, you can use
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], 'To Format'!R2C1:R" & lastRow & "C20, 10, FALSE)"