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








0
Utf
12/23/2009 8:21:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
821 Views

Similar Articles

[PageSpeed] 43

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
> 
> 
> 
> 
> 
> 
> 
> 
0
Utf
12/23/2009 9:01:02 PM
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
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
0
Utf
12/30/2009 1:49:01 PM
Reply:

Similar Artilces:

Copying and pasting a series of cells
When I copy and paste the following cell > =IF('13'!B13>0,'13'!B13,NA()) to the remainder of the days of the month, what I get is > =IF('13'!B14>0,'13'!B14,NA()) > =IF('13'!B15>0,'13'!B15,NA()) > etc But what I want to get is > =IF('14'!B13>0,'14'!B13,NA()) > =IF('15'!B13>0,'15'!B13,NA()) > etc I'd even settle for > =IF('13'!B13>0,'13'!B13,NA()) > =IF('13'!b13>0,'13'!B13,NA()) > etc whereafter I could just modify the fo...

Count with Hidden cells
Is there a way to count the number of text filled cells excluding the cells that I have hidden? Depends on the version of excel, excel 2003 works like =SUBTOTAL(103,A1:A10) won't count hidden cells -- Regards, Peo Sjoblom (No private emails please) "Stretch" <Stretch@discussions.microsoft.com> wrote in message news:9F387E47-FC1F-4AEB-8A6C-2B37BC012DD1@microsoft.com... > Is there a way to count the number of text filled cells excluding the > cells > that I have hidden? What does the 103 stand for? "Peo Sjoblom" wrote: > Depends on the v...

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Formulas do not work in excel.
Formulas have been working but suddenly they will not add or subtract unless we click on the target cell. This happens in all excel files even in new worksheets. Try going to tools-option-calculation and click automatic if it is on manual. Let me know if it works. B "Flintlock98" wrote: > Formulas have been working but suddenly they will not add or subtract unless > we click on the target cell. This happens in all excel files even in new > worksheets. ...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

PROTECTING FORMULAS #2
I have a sheet where when opened, it runs an auto macro to move cell contents from one column to another and uses a formula to do some calculations. If I select the columns I want unlocked, then protect the sheet and try to run the macro, I get an "run time error 1004 unable to set the numberformat property of the range class" When I debug the following is highlightd "Selection.NumberFormat = ";;;" " I was a Lotus user and in Lotus I could select a range of cells or columns to protect, I'm having a hard time to get Excel to do the same. I appreciate th...

Change operater in Excel formula
Hello, depending on a certain circumstances, a formula must change from "X = Y" to "X <> Y". Can this be done (e.g. using "if", or otherwise) ? Thank you in advance. Merry Christmas, H.G. Lamy You didn't give us a lot to work with, but I think you are looking for something like this... =IF(<<your condition>>,<<formula with x=y>>,<<formula with x<>y>>) -- Rick (MVP - Excel) "H.G. Lamy" <Enterplan@web.de> wrote in message news:upAoGxlhKHA.6136@TK2MSFTNGP04.phx.gbl.....

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

VBA Help Please
I have a data entry sheet with a column called �Priority� and a colum called �Start Flag�. I have a chart (not a PivotChart) which is located as an object in spreadsheet. I am trying to develop VBA code which will do the following: Upon activation of the spreadsheet containing the chart: Check each row (record) of the data entry sheet and count the record where Priority = �E� and Start Flag = �TRUE� Enter the result in a certain cell of the spreadsheet Check each row (record) of the data entry sheet and count the record where Priority = �O� and Start Flag = �TRUE� Enter the result in a cer...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Worksheet name in cell formula
I asked this question a while ago and got a prompt answer which I thought was what I wanted but alas its not... I want to be able to change my worksheet names.....ie: from Sheet 1, Sheet 2, etc etc.....to something more meaningful.....eg Sales, Expenses....etc etc.... and have these changes reflect on the worksheet. For example, I might want Sheet 1 Cell A1 to say.....This is the Sales worksheet (assuming I have renamed it to Sales). The answer I was given some time back worked.....but it changed the formula on every worksheet to reflect the name of the last changed sheet. By this I mean.....

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

VLOOKUP and cell color problem.
I have a spreadsheet to create a quotation/estimate and I use the VLOOKUP function to retrieve the price of an item on another worksheet. In that worksheet, I have several pricelists from various suppliers but one column gives me the lowest price and changes the cell color associated with the supplier so I know from which supplier the price comes from. Now my main quotation page displays the cheapest price but does not pass along the cell color which I need so i can tell, by looking at the quotation, which supplier i need to order each item from. Is there a way to pass along not only the va...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Colour numbers within my formulae
I have been playing around with the '&' function, allowing me to mix text with calculations and cell references. I have the following at the moment: ="Of the "&TEXT(J954,"�#,0.00")&" made this year, I have managed to save "&TEXT(100/J954*J955, "#,#0.0")&"%" Which gives me a cell displaying the following: Of the �0.00 made this year, I have managed to save 0.0% What I would like to do is show the numbers (�0.00; 0.0%) in the colour blue. I don't want to add conditional formatting, just apply a colour so t...

Font in Formula Bar
Greetings... I just started using Microsoft 2003. In Excel, I can't read text that is in the Formula Bar because it is i a weird small font. I looked everywhere to figure out how to change th font in the Formula Bar, but i can't find it anywhere. What font does it use? Is it based on an option in Microsoft or is i based on something to do with the Desktop settings...i've looke everywhere for an answer to this. Thanks for any help. jdkuhndo -- jdkuhndo ----------------------------------------------------------------------- jdkuhndog's Profile: http://www.excelforum.com...

protecting formulas
I am trying to find a way to protect formulas on a spreadsheet whil still allowing users to put in new data. Is there a way to do this? am using excel 200 -- Message posted from http://www.ExcelForum.com Cherilyn unlock the cells where you want input and then protect the sheet with a password. Format | Cells... | Protection tab | Locked = false (not ticked) Regards Trevor "Cherilyn >" <<Cherilyn.16krpe@excelforum-nospam.com> wrote in message news:Cherilyn.16krpe@excelforum-nospam.com... > I am trying to find a way to protect formulas on a spreadsheet while ...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Topic: Oh! NOOO! APOSTROPHE FOUND RE: Replace and Removing SQL VBA HELP!!!
Dear All,All nigth long and still do not know how to fix this query,This works fine until finds an apostrophe and I guess any otherstrange characterRun-time error '3075':Syntax error (missing operator) in query expression "C:\DVD APPZX\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 AudioDriver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 ANDTempInfo_Tbl.DirFolder Like 'DIR*"The code replace in one step all the records from my TblTempInfo_Tbl:Actual Record: DIR Dups V.1.0\bas\2006\<Drive>:\Path\Folder\Folder....After Replace: C:\Dups V.1.0\bas\2006If ...

financial excel formula
Trying to use excel to determine the % return on a stock from the tim you purchase until you sell, adjusted for time. ie., key in Pv o money today; input Fv or (the selling price); enter the amount of tim expressed in months or years (N); calc the rate of return %? p $50.00 FV $74.00 N 3 years % return =??? using a texas instruments ba-35 calculator th answer is 13.96%. Can't get this out of excel, any suggestions -- Message posted from http://www.ExcelForum.com =RATE(3;0;-50;74) The financial function follow the rule that incoming and outgoing money are different in sign. Hence...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Change to formula
I am using this formula to display the current date on a sheet, I require it to show the actual day before, what do I need to change. =IF(AND(B6="",B4<>""),NOW(),IF(AND(B4<>"",B6<>""),B6,"")) =IF(AND(B6="",B4<>""),TODAY()-1,IF(AND(B4<>"",B6<>""),B6,"")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "bbc1" <bbc1@discussions.microsoft.com> wrote in message news:9F920C16-159A-478D-AEC3-81BD6ABC5...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

Shrink Cells
Hi all, If I have a range of cells, say 4 x 4, can i shrink each cell data to smaller range of cells, say 3 x 3/2 x 2? Thanks -- Message posted from http://www.ExcelForum.com kaon wrote: > Hi all, > > If I have a range of cells, say 4 x 4, can i shrink each cell data to > a smaller range of cells, say 3 x 3/2 x 2? I really, really don't understand what you mean. Please give an example. -- To top-post is human, to bottom-post and snip is sublime. One try .. Suppose the 4 x 4 range is A1:D4 Select A1:D4 and click Copy Click on a cell outside of the range, say on A7 ...