VBA dynamic cell reference in cell formula

Question

I'm trying to convert this formula "=RIGHT(G7,LEN(G7)-LEN(OFFSET(H7,0,-7)))" into VBA and wherever it references cell H7 I want it to use ActiveCell instead as this formula is used in a loop and is entered in a different row each time. Hope I explained my predicament articulately.

My attempt at converting the formula ended with:

ActiveCell.Formula = "=right(offset(" & ActiveCell & ",0,-1),len(offset(" & ActiveCell & ",0,-1)-len(offset(" & ActiveCell & ",0,-7))"

which gives me Run-time error '1004': Application-defined or object defined error.

Scott Craner Scott Craner · Accepted Answer · 2016-05-03T20:00:38

This is what FormulaR1C1 was made for:

Sub foooo()

Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("H1:H100").FormulaR1C1 = "=RIGHT(RC-1,LEN(RC-1)-LEN(RC-7))"

End Sub

It will do the whole range at once.

If you really want to loop then:

Sub foooo()
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
For i = 1 To 100
    ws.Cells(i, "H").FormulaR1C1 = "=RIGHT(RC-1,LEN(RC-1)-LEN(RC-7))"
Next i
End Sub

The problem with your formula is that the default characteristic of a range object is its value not its address. so:

ActiveCell.Formula = "=right(offset(" & ActiveCell.Address(0,0) & ",0,-1),len(offset(" & ActiveCell.Address(0,0) & ",0,-1)-len(offset(" & ActiveCell.Address(0,0) & ",0,-7))"

Though I would avoid a column full of OFFSET Functions. OFFSET is a volatile function in that it calculates every time excel calculates regardless if any of the data to which it refers has changed. A column full of these will slow down the calculation times.

VBA dynamic cell reference in cell formula

1 Answers