Linking and equation with VBA

NPK

Board Regular
Joined
Jun 5, 2008
Messages
57
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.

Code:
 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
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

NPK

Board Regular
Joined
Jun 5, 2008
Messages
57
Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Nich

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

Code:
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

Board Regular
Joined
Jun 5, 2008
Messages
57

ADVERTISEMENT

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:

Code:
 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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

Board Regular
Joined
Jun 5, 2008
Messages
57
Thank you very much. I will play with this format and see what happens.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,157
Members
416,368
Latest member
PaoloC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top