Excel VBA: cell referencing

Question

I have a problem with cell referencing and I don't know if a solution exists.

Using VBA code I'm writing into a cell the reference to a different cell using the syntax:

activecell.formula = "=" & Cells(A1).address

Now I need to copy this formula for thousands of cells. Each cell should refer to a sequential cell (A1,A2,A3....A120..).

The problem I have is that using

Cells(A1).address

it fixes the Cells(A1). When I use the autofill function, I have thousands of cells all referencing the same cells(A1), while I would need the reference to be dynamic and every time referencing the next cell (A2,A3,A4..). And I need to use the autofill function because if I write the formula on each cell using a loop and fixed reference, it would take hours for the code to run (there are thousands of cells as explained before)

I should then use the relative reference format:

activecell.formulaR1C1 = ...

but the cells(A1) are not always at the same distance from my activecell.

via VBA I can calculate how far each time it is from my activecell, but how can I use this data with R1C1 notation?

so, lets say that using

row.count

function I can calculate the first cell to which I want to refer to,(this time) is on row 100 and column 3. So, I have: lastrow = 100, column = 3. Please note, the row 100 vary every time I run the macro. My activecell is cells(5,5)

this is what I would like to use:

Sub problemsolved ()

Dim lastrow as string
Dim RowL as string
Dim CoL as string

lastrow = activesheet.cells(rows.count, 1).End(xlUp).row


cells(5,5).select

Rol = lastrow - 5
Col = 3 -5


activecell.formulaR1C1 = "=R[Rol]C[Col]"

selection.autofill Destination:=Range(cells(5, 5), cells(10005, 5))

end sub

assuming lastrow is = 100 I need to have the following result:

cell(5,5): =cells(100,3)
cell(6,5): =cells(101,3)
cell(7,5): = cells(102,3)
.....
cell(10005,5): = cells(10100,3)

of course, I've tried the syntax:

activecell.formulaR1C1 = "=R[lastrow]C[3]"

but it doesn't work, so how can I do it? anybody has an idea?

thanks

Assume people are too busy to fully read all of this. You should also say what "doesn't work". i.e., what do you want to do, what have you tried, and what results did you get. — user1531971

Scott Craner Scott Craner · Accepted Answer · 2017-12-06T18:25:31

Use:

With ActiveSheet
    .Range(.Cells(5,5),.Cells(10005,5)).FormulaR1C1 = "=R[" & RowL & "]C[" & col & "]"
End With

no need for autofill

So

Sub problemsolved ()

Dim lastrow as Long
Dim RowL as Long
Dim CoL as Long

lastrow = activesheet.cells(rows.count, 1).End(xlUp).row



Rowl = lastrow - 5
Col = 3 -5


With ActiveSheet
    .Range(.Cells(5,5),.Cells(10005,5)).FormulaR1C1 = "=R[" & RowL & "]C[" & col & "]"
End With 

end sub

Excel VBA: cell referencing

1 Answers