CODEWITHSUNDEEP
arraysvbaruntime-error
Migarisa
Migarisa

Reputation: 15

Average VBA function

I'm getting

Run-time error '424': Object required"

every time I try this, I don't get why!

Dim CCAnual(1 To 200) As Currency
For i = 1 To 188
    Set CCAnual(i) = Applications.WorksheetFunction.Average(Worksheets(3).Range(Cells(i + 2, 5), Cells(i + 14, 5)))
Next i
For i = 189 To 200
    Set CCAnual(i) = Applications.WorksheetFunction.Average(Worksheets(3).Range(Cells(i, 5), Cells(200, 5)))
Next i

What am I doing wrong?

I've tried it both defining the worksheet as it is, with ActiveWorksheet and with nothing at all.

Upvotes: 1

Views: 246

Answers (1)

Vasily
Vasily

Reputation: 5782

1st issue is here CCAnual(i) = Applications.WorksheetFunction

use this:
CCAnual(i) = WorksheetFunction.Average(Range(Cells(i + 2, 5), Cells(i + 14, 5)))

or this:
CCAnual(i) = Application.Average(Range(Cells(i + 2, 5), Cells(i + 14, 5)))

or this:
CCAnual(i) = Evaluate("=Average(" & Range(Cells(i + 2, 5), Cells(i + 14, 5)).Address & ")")

also you should keep in mind that you may be faced with 2nd issue, is that .Average() does not work with empty cells, so at least one of the cells should be numeric value

so finally your code should be something like this:

 Sub test()
     Dim CCAnual(1 To 200) As Currency
     For i = 1 To 188
         If WorksheetFunction.CountA(Range(Cells(i + 2, 5), Cells(i + 14, 5))) = 0 Then 
             Exit For
         Else
             CCAnual(i) = Application.Average(Range(Cells(i + 2, 5), Cells(i + 14, 5)))
         End if
     Next i
     ''''''''''
 End Sub

Upvotes: 1

Related Questions