CODEWITHSUNDEEP
vbaexcel
Laura
Laura

Reputation: 103

VBA for loop error

I have this loop to take groups of 4 cells in one worksheet and average them into another worksheet. When the one line reads z = z+3 it runs, but if I change it to z = z+4 it doesn't (Runtime error 1004 Unable to get the Average property of the WorksheetFunction class). Why is this?

Dim summary As Worksheet
Set summary = ThisWorkbook.Sheets("Sheet3")

Dim cost As Worksheet
Set cost = ThisWorkbook.Sheets("Sheet4")  

Dim y As Integer
Dim z As Integer
z = 2

For y = 2 To 17

    cost.Cells(y, 3) = Round(Application.WorksheetFunction.Average(Range(summary.Cells(4, z), summary.Cells(4, (z + 3)))), 0)
    z = z + 4
Next y

Upvotes: 0

Views: 56

Answers (1)

CLR
CLR

Reputation: 12254

If you run your routine with z=z+3, then the final (where y=17) area averaged is $AX$4:$BA$4

However, if with z=z+4, all those extra cells mean the final area averaged is $BN$4:$BQ$4.

As you've stated in your comments that your data only extends to $AX$4, once y reaches 14 all of the cells being averaged are empty, and the function fails.

Upvotes: 1

Related Questions