using variables with built-in functions 1

[ntolani]

I populate a column with data through a loop. I refer to the column with a variable, y.
For ....
Cells(x, y).Value = Data(z)
x = x + 1
Next

I want to use Excel's built-in Average and Stdev functions to calculate stats at the end of the data in each specific column....but I want to use my variables within the function and I'm confused with the syntax.

Cells(x, y).Select
ActiveCell.Formula = "AVERAGE(y:y)"

This doesn't work because it simply takes the average of column Y and not the column that is represented by the variable y. I've played around moving the quotes and using & before the variables, but it just doesn't seem to work. If anyone could help with the syntax of this statement....or perhaps tell me a different way of going about it.....I would greatly appreciate it.

Thank you,
Neil

 

[sfvb]

Either
ActiveCell.Formula = "AVERAGE(" & y & ":" & y & &quot"
or
ActiveCell.Formula = "AVERAGE(" & CStr & ":" & CStr & &quot"
should work

 

[ntolani]

Hi,
Thanks for the help. That worked almost perfectly. The syntax is correct, however...whereas before I was able to populate the columns with y = integer, it is not letting me perform the calculations with an integer. The result of the code you helped me with is "AVERAGE(1:1), AVERAGE(2:2), AVERAGE(3:3), ...." (which is sort of what I would expect), but why doesn't it take the Average of column 1, 2, 3,...? Is this a case where it needs the Column letter (AVERAGE(A:A))? Is there a way around this?
Thanks again,
Neil

 

[sfvb]

I think you need a slightly different formula, because you have a circular reference. You're in a cell in column y and you want to average all the cells in column y.

I think you either need to average the column first and then assign it to the active cell, like this:
ActiveCell.Value = WorksheetFunction.Average(Columns)

or you could average all the cells above the current cell, like this:
ActiveCell.FormulaR1C1 = "=AVERAGE(R1C" & y & ":R" & x - 1 & "C" & y & &quot"

 

[ntolani]

Thanks once again. Both of your methods work. I prefer the 2nd method only because after the data has been processed, clicking in that cell shows you the formula that was used. The first method works perfectly except that only the calculated value is displayed, and not the formula. Thanks so much again, I appreciate it.
-Neil