How to define a variable range and use it.

Hello,

I have a noob question pls:

I want to define a variable range and use it to calculate averages. Here is what I have:

Dim rng1 as range
Range("D2").Select
Set rng1 = Range(Selection, Selection.End(xlDown)).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(rng1)"

But it doesn't work and keeps erroring out at average. Any ideas?

Thanks,

Which cells are you trying to average and in which cell do you want the formula to go? The code below creates a formula in D2 which computes the average of all cells in column D from D3.

Please Login or Register  to view this content.

I have cells in column D with the first one being a header and what I am basically doing is wanting to calculate the average of everything below that and place it at the end of the selection (which is the end of the data in that column). I tried this:

Please Login or Register  to view this content.

But I still got an error 424. There are 30 records in the column and the 32nd cell is where I am placing the formula. thats where you see the additional cells(j,4) comes into play.

Thanks

Try something like this...

Please Login or Register  to view this content.

That works great! Thanks!

Stupid noob question though. What does it actually do? I know that the Lastrow is used to get the last row in the column. Couldn't get the line after that.

Thanks

Originally Posted by pharaz

That works great! Thanks!

Stupid noob question though. What does it actually do? I know that the Lastrow is used to get the last row in the column. Couldn't get the line after that.

Thanks

You're welcome.

The last line puts the Average formula in the cell below the last used row in column D (Lastrow + 1)

This is a shorter version you could use...
Range("D" & Lastrow + 1).Formula = "=AVERAGE(D3:D" & Lastrow & ")"