Solved

Finding Average using VBA

Posted on 2013-06-25
4
225 Views
Last Modified: 2013-06-25
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
Comment
Question by:greg_c
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Dushan911
Comment Utility
0
 

Author Comment

by:greg_c
Comment Utility
Run-time error '438':

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

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:greg_c
Comment Utility
Perfect.  Thank you.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now