• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
greg_c
Asked:
greg_c
  • 2
1 Solution
 
Dushan De SilvaTechnology ArchitectCommented:
0
 
greg_cAuthor Commented:
Run-time error '438':

Object doesn't support this property or method.
0
 
Patrick MatthewsCommented:
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
 
greg_cAuthor Commented:
Perfect.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now