Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula in cells from VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula in cells from VBA
ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])"
Should be ActiveCell.FormulaR1C1 = "=Average (RC[-3]:RC[-1])" -- HTH, Barb Reinhardt "Mouimet" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula in cells from VBA
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 "Barb Reinhardt" wrote: ActiveCell.FormulaR1C1 = "=Average (RC[-3:RC[-1])" Should be ActiveCell.FormulaR1C1 = "=Average (RC[-3]:RC[-1])" -- HTH, Barb Reinhardt "Mouimet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert formula and copy to a range of cells above | Excel Programming | |||
Blocked from Inserting individual cells or Insert Cut Cells | Excel Discussion (Misc queries) | |||
Help with the formula to insert cells | Excel Worksheet Functions | |||
Insert Formula and Copy to other cells | Excel Discussion (Misc queries) | |||
How to insert formula to a range of cells from VBA? | Excel Programming |