CODEWITHSUNDEEP
vbaexcel
Dan S
Dan S

Reputation: 147

Average a range using an (i,j) format

I'm tearing my hair out. I've tried every variation I can think of on:

cell = Application.WorksheetFunction.Average((i + 24, j + 2),(i + 70, j + 2))

Any help appreciated!

Upvotes: 2

Views: 322

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

You need to change the syntax slightly and set the base values of i and j: 

    Sub dural()
    Dim i As Long, j As Long, v As Double

    i = 1
    j = 1
    cell = Application.WorksheetFunction.Average(Cells(i + 24, j + 2), Cells(i + 70, j + 2))
    MsgBox cell
End Sub

enter image description here

EDIT#1:

I hope this version makes my comment a little more understandable:

Sub dural()
    Dim i As Long, j As Long
    Dim cell As Double, r As Range
    '
    '   The values of i, j below are just demo values
    '
    i = 1
    j = 1
    Set r = Range(Cells(i + 24, j + 2), Cells(i + 70, j + 2))
    cell = Application.WorksheetFunction.Average(r)
    MsgBox i & "," & j & vbCrLf & r.Address(0, 0) & vbCrLf & cell
End Sub

Upvotes: 5

Chrismas007
Chrismas007

Reputation: 6105

Here is what you want:

Cells(i + 100, j + 3).Value = Application.WorksheetFunction.Average(Range(Cells(i + 24, j + 2), Cells(i + 70, j + 2)))

Upvotes: 3

Forward Ed
Forward Ed

Reputation: 9874

cell = Application.WorksheetFunction.Average(range(Cells(i + 24, j + 2),cells(i + 70, j + 2))

This is the full change that findwindow suggested.

Upvotes: 2

Related Questions