User Input for Formula

[stenlake1]

ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",((RC[-2]-R[-1]C[-2])*(RC[-2]-R[-1]C[-2])))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",((RC[-2]-R[-1]C[-2])*(RC[-2]-R[-1]C[-2])))"
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",((RC[-2]-R[-1]C[-2])*(RC[-2]-R[-1]C[-2])))"
Range("I3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",(SQRT(RC[-2]+RC[-1])))"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC1="""",R[-1]C1=""""),"""",(RC[-1]/1000))"
Range("K3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",((RC[-9]-R[-1]C[-9])/10))"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC1="""",R[-1]C1=""""),"""",(RC[-1]-RC[-2]))"
Range("M3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC1="""",R[-1]C1=""""),"""",((RC[-2]-RC[-3])/RC[-2])*100)"
Range("G3:M3").Select
Selection.AutoFill Destination:=Range("G3:M1504"), Type:=xlFillDefault

I have a problem. For the highlighted line in green - I currently have: RC[-9]-R[-1]C[-9])/10). I now need to change it so there is a user inputted variable so i would get: (RC[-9]-R[-1]C[-9])*<User value>)/1000. Any easy way of doing this? The other problem is that the user value might change down the sheet! ahhhh!

[unmarkedhelicopter]

Your ,"""", should be ,"",
No there is no way using standard formula.
You don't say when you need this user input, what the default would be, can this be stored in a hidden column, does it happen every time the user opens the sheet or just when the user drags the formula down ?
You 'could' do it with some standard formula and some change event code but you would have to change the design of your sheet a bit.
Do you always use relativistic references ? I'm not complaining, it's just that many people find them confusing. They are excellent for writing formulae to be implemented in code though.

[stenlake1]

Hi,

Thank you for responding. I think in my heart I knew what I was asking couldn't be done, but was hoping I was wrong as it now involves me redoing the code lol.

Ok what I would like the code to do is scroll through column "J" and if there is a value in there then in the adjacent cell in column "K" do the formula:

So I need the user to be prompted for a value - oh and the user needs to be prompted for the value everytime the value in column "A" changes!

RC[-9]-R[-1]C[-9])*<User value>)/1000

Any help would be much appreciated.

Kind regards

[unmarkedhelicopter]

And storing the value ???

[stenlake1]

The value does not need to be stored, merely used in the equation for those particular values in column "A"

[unmarkedhelicopter]

Okay, so I supose we could write it into the formula based on a changed value in column j ?
Okay I'll have a think.

[unmarkedhelicopter]

There is no example of what you want in the file you posted.
Which sheet ? Where is the data ? Where is an example formula ?
I assume the user knows to enter a value (say) between 1 and 1000 ?
Or is the user you ?