OK, here is what I am doing...

I am opening up a csv file, forcing the B-column to be text. I copy all the
data from there into another excel sheet. I use a macro to turn that data
into a pivot table. This table varies in rows and columns depending on the
amount of data. I want to put an average formula at the bottom of each
column that only averages the above cells that are in a certain value range
(example: average everything between 5 and 45). I know this has to be done
with an array formula. The problem I have is getting the macro to put the
formula in the sheet. I need the macro to be able to change the range that
it puts in the formula to suit the number of rows(example: sometimes the
range will be B5:B30 other times B5:B45). I already have the values 30 or 45
from the example counted in the macro, can I use them as a variable in the
formula somehow? Once I get the macro to do this part correctly then I need
to be able to fill across the formula to the end of the columns. After that,
I need to put in a rank formula under the average formula to rank all of the
averages and fill that across. It would be nice to have the averages/rank
update if I need to turn off any columns in the pivot table. Maybe I should
do that with a seperate macro? I realize this is a lot to ask for help on
but if I could just figure out how to have the macro put a different range in
the formula that would be a huge help. Thanks to everyone for all of their
help.

Dan

example
Sub test2()
Dim firstrow As Long
Dim lastrow As Long
Dim targetcolumn As Long
Dim targetrow As Long

firstrow = 5
lastrow = 45

targetcolumn = 4
targetrow = lastrow + 5
Cells(targetrow, targetcolumn).FormulaR1C1 = _
"=SUM(R" & firstrow & "C:R" & lastrow & "C)"

End Sub

HTH


"Huge project" wrote:

OK, here is what I am doing...

I am opening up a csv file, forcing the B-column to be text. I copy all the
data from there into another excel sheet. I use a macro to turn that data
into a pivot table. This table varies in rows and columns depending on the
amount of data. I want to put an average formula at the bottom of each
column that only averages the above cells that are in a certain value range
(example: average everything between 5 and 45). I know this has to be done
with an array formula. The problem I have is getting the macro to put the
formula in the sheet. I need the macro to be able to change the range that
it puts in the formula to suit the number of rows(example: sometimes the
range will be B5:B30 other times B5:B45). I already have the values 30 or 45
from the example counted in the macro, can I use them as a variable in the
formula somehow? Once I get the macro to do this part correctly then I need
to be able to fill across the formula to the end of the columns. After that,
I need to put in a rank formula under the average formula to rank all of the
averages and fill that across. It would be nice to have the averages/rank
update if I need to turn off any columns in the pivot table. Maybe I should
do that with a seperate macro? I realize this is a lot to ask for help on
but if I could just figure out how to have the macro put a different range in
the formula that would be a huge help. Thanks to everyone for all of their
help.

Dan