Absolute Referencing in a VLookup in VBA

[kimpy318]

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 vlookup formula to the rest of the column. 

More particularly, my code looks as follows:

Dim lastRow As Long

lastRow = Cells(Rows.Count, "C").End(xlUp).Row

...

ActiveCell.Formula = "=VLOOKUP(RC[-5], 'To Format'!RC[-7]:R[11]C[12], 10, FALSE)"

Selection.AutoFill Destination:=Range("H2:H" & lastRow)

Range("H2:H" & lastRow).Select. 

where lastRow is how I dynamically autofill my columns to different data sizes and 'To Format' is the sheet I am referencing to get my range of cells. 

Does anyone know how to make is so that in the Vlookup, the range of cells from To Format is absolute referencing A2 to T[lastRow]?

[#2]

@kimpy318 

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], 'To Format'!R2C1:R1000C20, 10, FALSE)"

[#3]

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?

[#4]

@kimpy318 

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.

[#5]

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?

[#6 · Accepted solution]

@kimpy318 

OK, you can use

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], 'To Format'!R2C1:R" & lastRow & "C20, 10, FALSE)"

[#7]

This is perfect! Thank you so much!