Forum Discussion
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 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]?
OK, you can use
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], 'To Format'!R2C1:R" & lastRow & "C20, 10, FALSE)"
6 Replies
- 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.
