How can I use VBA to create an =AVERAGE formula in a dynamic cell with a dynamic range?

Question

I'm trying to write a Sub that will allow me to replace a formula for every time the macro is run.

I'm able to select the range I want using:

Range("A3").Select
Selection.End(xlDown).Select
Range("B5", ActiveCell.Offset(-1, 1)).Select

And I'm able to find the cell in which I want the formula using:

Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 1).Activate

Is there a way I can make a formula that says =AVERAGE([selected range])?

Note: I do not want to just have the value in the cell. I need to have it so there is an active formula showing the results.

See [How to avoid using Select in Excel VBA - Stack Overflow](https://stackoverflow.com/q/10714251/4088852). Once you have a `Range` containing the cells you want to average, you can just use something like `=AVERAGE(" & rng.Address & ")"` — Comintern, Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that. — QHarr, Nov 26 '18 at 20:31

score 3 · Answer 1 · edited Nov 27 '18 at 08:07

3

It would seem you are trying to do something like this:

Dim lrows As Long

lrows = Range("A3").End(xlDown).Row - 1

Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"

You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.

edited Nov 27 '18 at 08:07

Pᴇʜ

answered Nov 26 '18 at 20:29

Michal Rosa

score -1 · Answer 2 · edited Nov 21 '22 at 01:04

-1

You can also use the formula. worksheetfunction.Average("Select range")

edited Nov 21 '22 at 01:04

Jeff Schaller

answered Nov 14 '22 at 03:41

Allwin M

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '22 at 11:11

2 Answers2