Linking and equation with VBA

[NPK]

As part of a called module action, I need to be able to call a single equation stored in a cell to be placed in multiple cells down a column corresponding to a value that is placed in the cel prior to it. As a practice, I stored the equation "=G2*2" in cell H2.

My code to call this equation in the column D is as follows.

 Range("D" & j + 1).Value = Sheets("Data Input").Range("H2").Formula

I was getting a #Ref due to other aspects of the code. I have since remedied that.

[Edit] The current issue is that the formula is not updating down the column, ie. each formula references "G2" rather than the cell next to it like I had planned. I expected the formula would work as a copy and paste operation in Excel would cause.

Thanks

 

[Richard Schollar]

Range("D" & j + 1).Formula = Sheets("Data Input").Range("H2").Formula

 

[NPK]

Range("D" & j + 1).Formula = Sheets("Data Input").Range("H2").Formula

Sorry Richard, I think I edited my prior post while you were responding.

I changed the .value operation for a .formula code and didn't experience any change in the output. It is always linking the exact equation in the cell rather than an updated reference for it's new position.

 

[Richard Schollar]

Hi Nich

If you want to apply it to lots of cells at the same time you should do it like this:

Range("D2:D100").Formula = Sheets("Data Input").Range("H2").Formula

If you do it like this, the formula should update on each new row.


</pre>

 

[NPK]

If you do it like this, the formula should update on each new row.

Thank you. It updates on every row and now after running it, I am sure I have asked the wrong question. I'll need to figure out how to condition the equation to only appear in certain rows corresponding to data entry in cell C, hence the attempt with:

 Range("D" & j + 1).Formula

I need the equation to appear only in column C when a number appears in column D along the same row.

 

[Richard Schollar]

Looping is fine in that case, although it would probably be easier to use R1C1 style notation. Make your formula in H2:

=$G2*2

then you can use:

Dim cell As Range, strFormula As String
strFrmula = Range("H2").FormulaR1C1  'assign R1C1 style formula to strFormula
For Each cell In Range("C2:C100")  'amend as appropriate
  If Isnumeric(cell.Offset(,1).Value) Then _
    cell.FormulaR1C1 = strFormula
Next cell

 

[NPK]

Thank you very much. I will play with this format and see what happens.