CODEWITHSUNDEEP
excelvba
Rigel Turdiu
Rigel Turdiu

Reputation: 15

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

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.

Upvotes: 0

Views: 699

Answers (2)

Allwin M
Allwin M

Reputation: 1

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

Upvotes: -1

Michal
Michal

Reputation: 5725

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.

Upvotes: 3

Related Questions