Finding Average using VBA

Posted on 2013-06-25
Last Modified: 2013-06-25

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). _
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.


Question by:greg_c
  • 2
LVL 17

Expert Comment

by:Dushan De Silva
ID: 39276983

Author Comment

ID: 39276987
Run-time error '438':

Object doesn't support this property or method.
LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 39276992
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


Author Closing Comment

ID: 39277003
Perfect.  Thank you.

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question