Excel VBA Variable Row reference in Formula

Question

I am using VBA to apply the following formula into a Cell. I'd like to replace the reference to Cell G11 with a Long that references the Row number of the ActiveCell. So instead of G11 it would be G55 if ActiveCell is on Row 55, or G27 if ActiveCell is on is Row 27.

Columns("A").Find("", Cells(Rows.Count, "A")).Offset(-1, 7).Formula = "=IF(G11=""Enter date"",""Don't edit cell"",TEXT(G11, ""dddd""))"

I did try this but got Object Defined Error:

Dim ActiveRowNumber As Long
ActiveRowNumber = ActiveCell.RowNumber

Columns("A").Find("", Cells(Rows.Count, "A")).Offset(-1, 7).Formula = "=IF(G11=""Enter date"",""Don't edit cell"",TEXT(""G"" & ActiveRowNumber, ""dddd""))"

Hi, yes, updated code to show what I tried, but got Object Defined Error... — pwm2017
range.formula = "=Sum(A1:A" & lastrow - 1 & ")" is an example of using a variable in your inserted formula. Note the position of the Quotations and the Ampersands. — Cyril
I'm guessing you want R1C1 notation Columns("A").Find("", Cells(Rows.Count, "A")).Offset(-1, 7).FormulaR1C1 = _ "=IF(RC[-1]=""Enter date"",""Don't edit cell"",TEXT(RC[-1], ""dddd""))" so that the formula in H123 refers to the cell in G123 — CDP1802

Cyril Cyril · Accepted Answer · 2020-03-09T17:46:31

Updated your posted code:

Dim ActiveRowNumber As Long
ActiveRowNumber = ActiveCell.Row

Columns("A").Find("", Cells(Rows.Count, "A")).Offset(-1, 7).Formula = "=IF(G" & ActiveRowNumber & "=""Enter date"",""Don't edit cell"",TEXT(G" & ActiveRowNumber & ", ""dddd""))"

You left the & " out after the ActiveRowNumber. The quotes around the G in Text(""G"" appears to break you.

Excel VBA Variable Row reference in Formula

1 Answers