Insert a formula in a range of cells via VBA using reference cells

1
votes

I have a code that loops through and calculates a formula for a column of inputs for a row of dates. It is rather slow looping and writing in VBA so I was hoping to instead paste the formula in the cells instead of the computed value. My plan is to create a range for the results and set the range equal to the formula.

However I am stuck with the formula. The formula uses the previous answer plus other data and I am stuck trying to create the formula for the range so the reference cells move for each cell in the range.

The formula will look like this for cell N7

   =IF(M7<>0,(M7-$D7)^(1/30.4),$B7*(1-$C7)^(1/$E7))

The formula will look like this when copied to cell O7

  =IF(N7<>0,(N7-$D7)^(1/30.4),$B7*(1-$C7)^(1/$E7))

For O8 it would look like this

  =IF(N8<>0,(N8-$D8)^(1/30.4),$B8*(1-$C8)^(1/$E8))

I understand how to loop through each cell and write the formula, but I don't think it would be much faster than calculating it and then writing the answer. I was hoping there is a way to set the range equal to a formula that would populate the correct cell references.

Would ActiveCell.Offset be a solution?

2
excelexcel-2010vba

2 Answers

1
votes

Use relative references:

.FormulaR1C1 = "=IF(RC[-1]<>0,(RC[-1]-RC4)^(1/30.4),RC2*(1-RC3)^(1/RC5))"

You can use this on a range to populate in one go if you need rather than looping through with something like this:

Range("N7:P20").FormulaR1C1 = "=IF(RC[-1]<>0,(RC[-1]-RC4)^(1/30.4),RC2*(1-RC3)^(1/RC5))"

R is row and C is column relative to self negatives have to be in []

1
votes

You can also do this by using A1 Notation.

Range("N7:O8").Formula = "=IF(M7<>0,(M7-$D7)^(1/30.4),$B7*(1-$C7)^(1/$E7))"

There are advantages using R1C1 Notation but for this one, you better off using this, easier to read. HTH.