Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Excel formulas in VBA
I would like to know how to use the standard Excel
formulas in VBA. For example, one function that I am trying to use is the average function. When I record a macro following the steps to perform this function it gives me the following: ActiveCell.FormulaR1C1 = "=AVERAGE(R[-31]C:R[-1]C)" The catch is that the column I need to average will vary in size. I can store the size of the column by indexing. My question is how do I place this variable in the formula above to calculate the average? Is it possible or is there another format that must be used? Any help would be greatly appreciated. Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Excel formulas in VBA
Sub tester3()
r1 = 3 r2 = 100 Cells(2, 5).FormulaR1C1 = "=Average(R" & r1 & "C:R" & r2 & "C)" End Sub Using absolute references -- Regards, Tom Ogilvy Jeff Armstrong wrote in message ... I would like to know how to use the standard Excel formulas in VBA. For example, one function that I am trying to use is the average function. When I record a macro following the steps to perform this function it gives me the following: ActiveCell.FormulaR1C1 = "=AVERAGE(R[-31]C:R[-1]C)" The catch is that the column I need to average will vary in size. I can store the size of the column by indexing. My question is how do I place this variable in the formula above to calculate the average? Is it possible or is there another format that must be used? Any help would be greatly appreciated. Thanks, Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Excel formulas in VBA
try like this Sub avera() Ran = Range(Cells(1, 1), Cells(14, 1)) T = Application.WorksheetFunction.Average(Ran) Cells(15, 1) = T End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
ms-excel guidence notes, complete formulas in excel, How to runmacros in Ms excel | Excel Discussion (Misc queries) | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Copying formulas from Excel 2003 to Excel 2007 | Excel Discussion (Misc queries) | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) |