copy formula to range

[wpryan]

Hi All,
I would like to copy a formula to a range of cells, the relevant code is as follows:

Dim cell as range
Dim wsData As Worksheet: Set wsData = Sheets("Data")
For Each cell in wsData.Range("B10:B45")
        Cells.FormulaR1C1 = "=IF(R[-1]C+20>720,MOD(R[-1]C,20),R[-1]C+20)"
Next cell

I get an error, "there isn't enough memory to complete this action."
...what's going on?

 

[Legacy 143009]

You have a typo Try:

cell.FormulaR1C1 = "=IF(R[-1]C+20>720,MOD(R[-1]C,20),R[-1]C+20)"

 

[Fluff]

It should be be cell.Formula, not cells. Otherwise you are trying to put the formula into every single cell on the sheet

 

[mohadin]

Hi
what about

Sub forma()
Dim cell As Range
Dim wsData As Worksheet
Set wsData = Sheets("Data")
With wsData.Range("B10:B45")
       .FormulaR1C1 = "=IF(R[-1]C+20>720,MOD(R[-1]C,20),R[-1]C+20)"
End With
End Sub

 

[wpryan]

You have a typo Try:

cell.FormulaR1C1 = "=IF(R[-1]C+20>720,MOD(R[-1]C,20),R[-1]C+20)"

Thanks, my bad!