Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable inside function range
Hi all,
I have an averaging macro working with the following line in it: ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])" the line averages 10 rows of data in the same column. What I want to do is change the "9" to a variable that I use elsewhere in the macro (boxsize) so that the number of averaged cells changes automatically. But when I substitute the variable (boxsize, which = 9) into the line above, I get an error. Could someone please tell me why it doesn't work and what can I do to fix it. Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable inside function range
I think you probably need to do something like this to create the formula:
ActiveCell.FormulaR1C1 = _ "=AVERAGE(RC[-1]:R[" & BoxSize & "]C[-1])" That'll put whatever value is contained in BoxSize into the formula rather than putting the word "BoxSize" into the formula and having Excel fail because it has no real idea of what the hell BoxSize is other than a word, as it would in this formula: "=AVERAGE(RC[-1]:R[BoxSize]C[-1])" "dna1711" wrote: Hi all, I have an averaging macro working with the following line in it: ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])" the line averages 10 rows of data in the same column. What I want to do is change the "9" to a variable that I use elsewhere in the macro (boxsize) so that the number of averaged cells changes automatically. But when I substitute the variable (boxsize, which = 9) into the line above, I get an error. Could someone please tell me why it doesn't work and what can I do to fix it. Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable inside function range
Thank you, what you suggested does indeed do what I want. Obviously I'm new
to VBA and don't quite understand the syntax. Why does: endaddr = Range(Active_addr).Offset(boxsize, 0).Address work with boxsize (being a variable = 9) and what I attempted to do in the Average line not work? And where in Help is its description located? Regards, "JLatham" wrote: I think you probably need to do something like this to create the formula: ActiveCell.FormulaR1C1 = _ "=AVERAGE(RC[-1]:R[" & BoxSize & "]C[-1])" That'll put whatever value is contained in BoxSize into the formula rather than putting the word "BoxSize" into the formula and having Excel fail because it has no real idea of what the hell BoxSize is other than a word, as it would in this formula: "=AVERAGE(RC[-1]:R[BoxSize]C[-1])" "dna1711" wrote: Hi all, I have an averaging macro working with the following line in it: ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])" the line averages 10 rows of data in the same column. What I want to do is change the "9" to a variable that I use elsewhere in the macro (boxsize) so that the number of averaged cells changes automatically. But when I substitute the variable (boxsize, which = 9) into the line above, I get an error. Could someone please tell me why it doesn't work and what can I do to fix it. Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable inside function range
I think the answer to both questions is that while in VB, the variable
BoxSize is evaluated to see what its value is before anything is done with it. In your question, the .Offset(boxsize, 0) portion is evaluated to determine what the value is, and it is found to be 9 and that's the actual value used to get the result. Same for the concatenated formula that I provided: BoxSize is evaluated and its current value (9) is used in the formula, not the literal words BoxSize. However, the way you wrote it initially, it is made a literal part of the formula. But back in the Excel worksheet, Excel doesn't know anything about a variable or name called BoxSize - it goes looking for it, doesn't find it and throws up in your lap. If you had had a cell named BoxSize in the workbook, it would have picked up the value from that cell and evaluated it and used its value in the formula. Hope this explanation helps clear things up a little for you. "dna1711" wrote: Thank you, what you suggested does indeed do what I want. Obviously I'm new to VBA and don't quite understand the syntax. Why does: endaddr = Range(Active_addr).Offset(boxsize, 0).Address work with boxsize (being a variable = 9) and what I attempted to do in the Average line not work? And where in Help is its description located? Regards, "JLatham" wrote: I think you probably need to do something like this to create the formula: ActiveCell.FormulaR1C1 = _ "=AVERAGE(RC[-1]:R[" & BoxSize & "]C[-1])" That'll put whatever value is contained in BoxSize into the formula rather than putting the word "BoxSize" into the formula and having Excel fail because it has no real idea of what the hell BoxSize is other than a word, as it would in this formula: "=AVERAGE(RC[-1]:R[BoxSize]C[-1])" "dna1711" wrote: Hi all, I have an averaging macro working with the following line in it: ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])" the line averages 10 rows of data in the same column. What I want to do is change the "9" to a variable that I use elsewhere in the macro (boxsize) so that the number of averaged cells changes automatically. But when I substitute the variable (boxsize, which = 9) into the line above, I get an error. Could someone please tell me why it doesn't work and what can I do to fix it. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a variable in the Range function | Excel Worksheet Functions | |||
how can i use countif function inside a filtered range | New Users to Excel | |||
How to know if a variable has been reset inside other function | Excel Programming | |||
Variable range in MAX-function | Excel Worksheet Functions | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) |