Populating formulas in a spreadSheet with VBA

[C Brandt]

My task is to create a moving average for stocks.
I have downloaded a table of daily prices.
Requested the period for the moving average in weeks
now I would like to create the formula to populate the adjacent column to
calculate the moving average:

10 SA = inputbox("Input Number of weeks to use in Moving average")
' weeks in average calculation
20 MA=SA*-5 ' Provides negative offset to the first
row of the moving average
30 Cells(SA*5+1,10).select ' SA times 5 days plus one row for
header = first row that qualifies for moving average.
40 ActiveCell.FormulaR1C1 = "=AVERAGE(R[MA]C[-3]:R[-1]C[-3])"
50 Selection.Copy
60 Range(Cells(FA * 5 + 1, 10), Cells(DataEnd, 10)).Select ' DataEnd
is the last row of valid data
70 ActiveSheet.Paste

When I try to create the formula, line 40, it gives me a runtime error of
1004 (Application-defined or object-defined error).

Is there a way to create the formula in the manner that I am attempting?

Getting older by the minute,
Craig

 

[Guest]

I have not looked to closely but the first thing that strikes me is that your
variable MA is contained in the quotes so it is part of the text string and
not the variable. Try this...

ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & MA & "]C[-3]:R[-1]C[-3])"

 

[C Brandt]

Jim:
That was it. 90% of the time I want to kick myself in the butt because the
problems are generaly very simple errors.

Thanks,
Craig

I have not looked to closely but the first thing that strikes me is that your
variable MA is contained in the quotes so it is part of the text string and
not the variable. Try this...

ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & MA & "]C[-3]:R[-1]C[-3])"

--
HTH...

Jim Thomlinson

My task is to create a moving average for stocks.
I have downloaded a table of daily prices.
Requested the period for the moving average in weeks
now I would like to create the formula to populate the adjacent column to
calculate the moving average:

10 SA = inputbox("Input Number of weeks to use in Moving average")
' weeks in average calculation
20 MA=SA*-5 ' Provides negative offset to the first
row of the moving average
30 Cells(SA*5+1,10).select ' SA times 5 days plus one row for
header = first row that qualifies for moving average.
40 ActiveCell.FormulaR1C1 = "=AVERAGE(R[MA]C[-3]:R[-1]C[-3])"
50 Selection.Copy
60 Range(Cells(FA * 5 + 1, 10), Cells(DataEnd, 10)).Select ' DataEnd
is the last row of valid data
70 ActiveSheet.Paste

When I try to create the formula, line 40, it gives me a runtime error of
1004 (Application-defined or object-defined error).

Is there a way to create the formula in the manner that I am attempting?

Getting older by the minute,
Craig