count and sum of dynamic range ????

[wraithlead]

Still trying to get my macro to work. at this rate, i'll either b
really proud or completely gray headed by time it works.

I get a runtime error '13': Type mismatch on the underlined text when
try to run the macro. it takes compares two cells in one row to tw
cells in the next row, and keeps a running sum of a third column unti
the two groups are no longer the same, then finds the average.

Range("BX8").Select

Do
Sum = 0
Count = 0

If ActiveCell.Offset(0, -74) & ActiveCell.Offset(0, -71)
ActiveCell.Offset(1, -74) & ActiveCell.Offset(1, -71) Then
Count = Count + 1
_Sum_=_ActiveCell.Offset(0,_-12).Value_+_1_
Else
Count = Count + 1
Sum = ActiveCell.Offset(0, -12).Value + 1
ActiveCell.Value = Sum / Count
Count = 0
Sum = 0
End If
ActiveCell.Offset(1, 0).Select


Loop Until ActiveCell.Offset(1, -74).Value = ""

End Su

 

[Tom Ogilvy]

if isnumeric(ActiveCell.Offset(0,_-12).Value) then
Sum = ActiveCell.Offset(0,-12).Value + 1
End if

however, if you are trying to accumulate a sum shouldn't it be
if isnumeric(ActiveCell.Offset(0,_-12).Value) then
Sum = ActiveCell.Offset(0,-12).Value + Sum
End if

and I suspect you shouldn't have the second

Count = Count + 1 right after the Else

 

[wraithlead]

Thanks,

That did it