0

I am trying to make a macro that allows me to use variables to alter cell references to apply a formula across how ever many cells I require. Here is what I have:

    Sub actuals()
'
' actuals Macro
'
Dim counter As Integer
For counter = 0 To 4

'
    `ActiveCell.FormulaR1C1 = _
        "=(IF('EAC with Labor Forecast OY1'!R[-7-counter]C[64+(5*counter)]=""No"",'EAC with Labor Forecast OY1'!R[25-counter]C[67+(5*counter)],'EAC with Labor Forecast OY1'!R[25-counter]C[64+(5*counter)])+IF('EAC with Labor Forecast OY1'!R[40-counter]C[64+(5*counter)]=""No"",'EAC with Labor Forecast OY1'!R[73-counter]C[67+(5*counter)],'EAC with Labor Forecast OY1'!R[73-conuter]C[64+(5*counter)]))"
    ActiveCell.Select`

    Selection.Offset(1, 0).Select

    Next counter

End Sub

I get an error that I assume is related to my use of the counter variable. Any help?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
PonziPlays
  • 1
  • Excel formulas need to reference specific cells. Excel does not know what 7-counter is. You need it to be 6, 5, etcin the formula. So "=(IF('EAC with Labor Forecast OY1'!R[" & -7-counter & "]... – JPortillo Feb 27 '20 at 20:30
  • Side note: you want to avoid [using select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Feb 27 '20 at 20:37

0 Answers0