Excel VBA: Using .formula with dynamic ranges

Question

I'm trying to average some data sets which are in their own columns. The number of data sets may change, so the number of columns I average needs to be dynamic.

Also, I have multiple sets of data sets on each sheet, and because the data sets are dynamic, I add columns to the sheet earlier in the code, so I can't hardcode the starting point for the data set. In other words, I can't just say...

Range("L5").formula = "=AVERAGE(H5:K5)"

... because I can't know for sure that the data sets start at H5. It might start at F5, or G5, etc.

So I'm trying pass the a starting point as an argument in an As Range variable, but I don't know how interface this with a .formula.

The following code doesn't work. I'm guessing because I can't put a range variable in a string like this.

Sub Average()
Dim c As Range

Set c = Range("B5:B10")

Range("F5").FormulaR1C1 = "=Average(" & c & ")"

Is there any way to use ranges with excel formulas like this?

Scott Craner · Accepted Answer · 2016-05-18 19:35:30Z

4

It is looking for the address and the default of a range object is value:

Range("F5").Formula = "=Average(" & c.Address(0,0) & ")"

answered May 18, 2016 at 19:35

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcucciboy2 · Accepted Answer · 2019-09-16 14:01:39Z

1

If the range to be averaged is the only data on the sheet, you can find the "last row" and "last column", then use those variables in the formula.

To find last row:

LastRow = Cells.Find("*", Range("A1"), xlFormulas, xlPart, _
        xlByRows, xlPrevious, False, False).Row

To find last column letter:

LastCol = Split(Cells(1, ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column).Address, "$")(1)

The formula in would change from:

"=AVERAGE(H5:K5)"

to:

"=AVERAGE(H5:" & LastCol & Last Row & ")"

edited Sep 16, 2019 at 14:01

Marcucciboy2

3,2673 gold badges22 silver badges41 bronze badges

answered May 18, 2016 at 19:52

TheAverageBear

1211 silver badge6 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Using .formula with dynamic ranges

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related