Formula with a variable in a mocro

[Guest]

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

 

[Guest]

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