Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas in VBA
Tom,
Thanks for the help - however, I don't always have a list which is fillled with numbers - It's basically a list that grows or shrinks (based on the number of years of data I have for a certain product.) and includes blank cells at times. What I do know is how many rows of data I'd need at any particular point. There may be times when I had to average a list of 10 rows, or at times an average of 6 rows. I need to display the formula as well - is there any way to do this? Thanks, Haas "Tom Ogilvy" wrote in message ... Assuming your data starts in Cell A1 and has no blank cells down to the last cell. Range("A1").End(xldown).offset(1,0).FormulaR1C1 = _ "=Average(R1C1:R[-1]C1)" or Cells(rows.count,1).End(xlup)(2).FormulaR1C1 = _ "=Average(R1C1:R[-1]C1)" Regards, Tom Ogilvy "Haas" wrote in message om... HELP!!! I need to show a formula in a cell which would calculate the average of a list of numbers. Recording a macro yielded the following results: Range("A25").Select ActiveCell.FormulaR1C1 = "=average(R[-18]C:R[-3]C)" Here's the problem: the list of numbers is always either growing or shrinking, so the formula would not always be in Cell A25, and we wouldn't always be offsetting by 18 rows, etc. This would be simple to do if I din't need to show the formula itself, but I have to. I tried the following: a = -18 b = -3 Range("A25").Select ActiveCell.FormulaR1C1 = "=average(R[a]C:R[b]C)" (This was done for example purposes - I will be using offsets to achieve selecting a growing or shrinking range...) I received an error message - I guess you can't use an R1C1 convention with variables. Is there another way to do this? I need to show the formula in the cell due to data manipulation purposes; also, this would be part of a program which would have other calculations and a requirement has been that formulas be shown. Any help on this matter will be greatly appreciated! Thanks, Haas |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) |