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)"
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)"
- This is perfect! Thank you so much!