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

1 Answers1