CODEWITHSUNDEEP
vba
lebout
lebout

Reputation: 85

VBA Call a Variable Range

I am trying to calculate a moving average (i.e. the average of B2:b4, b3:b5, etc.) but I cannot figure out how to call a variable range. How do I do tihs?

Sub SMA()
Dim SMA As Double

For Each cell In Range("b2:b8")

    cell.Offset(0, 1).Value = Application.Average(cell.Value, b5).Value

Next

End Sub

Upvotes: 0

Views: 275

Answers (1)

KekuSemau
KekuSemau

Reputation: 6856

Is this what you are looking for: ?

Dim cell As Range
For Each cell In Range("b2:b8").Cells
    cell.Offset(0, 1).Value = Application.Average(cell.Resize(3))
Next

However, you could insert this as a formula once and copy it down without VBA.

Upvotes: 1

Related Questions