CODEWITHSUNDEEP
vbaexcel
Max Hui
Max Hui

Reputation: 23

Unable to get the average property of the worksheetfunction class

Hi I'm trying to write vba to help me convert daily data into weekly data. I wrote this vba but said unable to get the average property of the worksheetfunction class. Is it possible someone can help me figure whats wrong? thanks!

Sub CopyData()
Dim z As Integer

  For z = 0 To 2000

   Set Rng1 = ActiveSheet.Range("D5:D11").Offset(7 * z, 0)

    Range("runningagain").Offset(z, 0) = Application.WorksheetFunction.Average(Rng1)

    Do Until IsEmpty(ActiveCell.Value)
      ActiveCell.Offset(1, 0).Select
    Loop

  Next z

End Sub

Upvotes: 2

Views: 14721

Answers (1)

user857521
user857521

Reputation:

The average function in Excel will produce a #DIV/0! error if the cells are empty so you need to check rng1 is not empty.

As you are taking the average then it's best to use Count instead of CountA

If Application.WorksheetFunction.Count(Rng1) > 0 Then
    Range("runningagain").Offset(z, 0) = Application.WorksheetFunction.Average(Rng1)
End If

Also, you may need to define Range("runningagain") properly, do you mean Range(runningagain)?

Upvotes: 3

Related Questions