CODEWITHSUNDEEP
excelvba
Amos
Amos

Reputation: 115

Averaging different length ranges in excel with VBA

I'm trying to write a short macro that includes a line that averages a range of cells. In each worksheet that I want to run the macro in the range of cells is a different length.

After running the macro the cell E1 contains "=AVERAGE(Rng)"

    Dim homeSheet As Worksheet
    Set homeSheet = ActiveSheet
    Dim lastRow As Long
    Dim Rng As Range
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Set Rng = Range("B2:B" & lastRow)
    Range("E1").Formula = "=Average(Rng)"
    Range("E2").Formula = "=STDEV(Rng)"
    Range("E3").Select
    ActiveWindow.SmallScroll Down:=-2
End Sub

I've also tried

Range("E1").Formula = "=Average(Range("B2:B" & lastRow))"

without trying to use Set Rng = Range("B2:B" & lastRow)

Upvotes: 0

Views: 238

Answers (1)

Netloh
Netloh

Reputation: 4378

You need to use Rng.Address in your formulas. Try to change your code into this:

Sub Avg()
    Dim homeSheet As Worksheet
    Set homeSheet = ActiveSheet
    Dim lastRow As Long
    Dim Rng As Range
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Set Rng = Range("B2:B" & lastRow)
    Range("E1").Formula = "=Average(" & Rng.Address & ")"
    Range("E2").Formula = "=STDEV(" & Rng.Address & ")"
    Range("E3").Select
End Sub

If you were to use the second method you have tried, you would need to change that line of code to:

Range("E1").Formula = "=Average(" & Range("B2:B" & lastRow).Address & ")"

Upvotes: 1

Related Questions