VBA for loop error

Question

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

do you have s numeric value that column ? maybe you have a string — Shai Rado
– Shai Rado, Commented Jun 29, 2017 at 10:42

CLR · Accepted Answer · 2017-06-29 11:39:33Z

1

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.

answered Jun 29, 2017 at 11:39

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA for loop error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related