Finding Average using VBA

Hi

For some reason I get an error message at the following line:

WorksheetFunction.Average(Cells(myCount, 2), Cells(myCount, 3))

below is the full code:

Sub Average()
Dim myCount As Integer, LastRow As Integer

LastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1). _
    End(xlUp).Row
   
For myCount = 2 To LastRow
With Worksheets("Sheet2")
    .Cells(myCount, 6).Value _
    WorksheetFunction.Average(Cells(myCount, 2), Cells(myCount, 3))
End With
Next myCount
End Sub

Could someone explain to me why.

Thanks

Greg
greg_cAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Two things wrong:

1) Missing an equal sign
2) Missing a qualifying reference for your Cells calls.

Fixing both:

With Worksheets("Sheet2")
    .Cells(myCount, 6).Value = Application.Average(.Cells(myCount, 2), .Cells(myCount, 3))
End With

Open in new window

0
 
Dushan De SilvaTechnology ArchitectCommented:
0
 
greg_cAuthor Commented:
Run-time error '438':

Object doesn't support this property or method.
0
 
greg_cAuthor Commented:
Perfect.  Thank you.
0
All Courses

From novice to tech pro — start learning today.