Averaging a variable length column

[Doug]

I'm creating a macro that will Open each spreadsheet in a
given directory and copy data from specific cells and
paste that data into a new spreadsheet. I have the macro
working fine for static number of spreadsheets. However,
I need to edit it so that it will work with any number of
spreadsheets I have in the directory. I have it working
except for two related functions. I need to get an
average of each column and the standard deviation.

I ran the macro recorder as I created the average so that
I would have code to start with, but it uses relative
addressing and when I replace the row number (R[-208]C)
with my variable that represents the number of rows it
gives me an error. I've also tried to use use the
following line, but all it does is populate the cell with
the text inside the quotes and does not perform the
calculation (varColumnNames is an array that scrolls
through the columns that need to be calculated, and all
variables are integers).

ActiveCell.FormulaR1C1 = Average(Cells(FirstRow,
varColumnNames(ColumnCount)), (Cells(LastRow,
varColumnNames(ColumnCount))))

Any help or direction would be appreciated.

 

[Tom Ogilvy]

vVal = -208
Sstr = "R[" & vVal & "]C"





MyAverage = Application.Average(Range(Cells(FirstRow, _
varColumnNames(ColumnCount)), Cells(LastRow, _
varColumnNames(ColumnCount))))
ActiveCell.Value = MyValue