Insert Formula in cells from VBA

[Mouimet]

Hi,
I have different data related together on a sheet.
I want to create a formula selecting the function depending the code I have
in cell A7
The formula in all cell should be like this
= Choose(A7,ATV,Connect,Sales,Hours, Closing)

Each option (ATV, Connect, etc) = "a function I need to create for each case"

In case the user select "1" in cell A7 to increase the ATV, the cell should
use the function ATV
Using a relative reference so I can use it everywhere
I tried this:

Function ATV()
ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])"
End Function

This mean calculate the average of the last 3 columns (months)

For testing in cell AL67 instead of
= Choose(A7,ATV,Connect,Sales,Hours, Closing)
I use directly
=ATV()

I tried also =ATV(AL67)

What I need to see is the answer of the formula =Average (RC[-3:RC[-1])
when in the cell I have =ATV function

Thanks

 

[Barb Reinhardt]

ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])"

Should be

ActiveCell.FormulaR1C1 = "=Average (RC[-3]:RC[-1])"

 

[Mouimet]

Hi Barb,
Thanks for the answer however,
I mistype the formula in my note. The formula was like you said. Sorry

Problem is when I type Estimate(al67) in a cell I get an #Value

Here the test I did
IN VBA the function is

Function Estimate()
ActiveCell.FormulaR1C1 = "=Average (RC[-3]:RC[-1])"
End Function


On the sheet:
the data are
(Formula will be here in
cell AL67)
Col: AI AJ AK AL

Row67 222 379 174 =estimate(AL67)


I should see the answer 258.33 (average of 222,379,174)
I just see #Value

THanks

ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])"

Should be

ActiveCell.FormulaR1C1 = "=Average (RC[-3]:RC[-1])"
--
HTH,

Barb Reinhardt

Hi,
I have different data related together on a sheet.
I want to create a formula selecting the function depending the code I have
in cell A7
The formula in all cell should be like this
= Choose(A7,ATV,Connect,Sales,Hours, Closing)

Each option (ATV, Connect, etc) = "a function I need to create for each case"

In case the user select "1" in cell A7 to increase the ATV, the cell should
use the function ATV
Using a relative reference so I can use it everywhere
I tried this:

Function ATV()
ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])"
End Function

This mean calculate the average of the last 3 columns (months)

For testing in cell AL67 instead of
= Choose(A7,ATV,Connect,Sales,Hours, Closing)
I use directly
=ATV()

I tried also =ATV(AL67)

What I need to see is the answer of the formula =Average (RC[-3:RC[-1])
when in the cell I have =ATV function

Thanks