Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 41
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert formula and copy to a range of cells above mickjjuk Excel Programming 2 December 4th 09 04:00 PM
Blocked from Inserting individual cells or Insert Cut Cells Ching-AHS Excel Discussion (Misc queries) 0 December 1st 09 06:47 PM
Help with the formula to insert cells Igneshwara reddy[_2_] Excel Worksheet Functions 5 March 23rd 07 06:14 PM
Insert Formula and Copy to other cells [email protected] Excel Discussion (Misc queries) 1 August 12th 05 08:51 PM
How to insert formula to a range of cells from VBA? crapit Excel Programming 10 March 18th 05 02:38 PM


All times are GMT +1. The time now is 10:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"