Average a changing range of values

[Richard]

I need to average utilisation values for server names, but the range of
values changes. Please can someone help with a macro for this.
Data example:
Time Duration Utilisation % Server Name
CW001P01-SH-C: CW001P01-SH-C:
2009/09/19 23:56 903 96.6814438
2009/09/20 00:11 899 96.68444105
2009/09/20 00:26 901 96.68782082
2009/09/20 00:56 927 96.69387136
2009/09/20 01:12 872 96.69638761
2009/09/20 01:26 902 96.69934001
2009/09/20 01:41 898 96.70246381
CW001P03-SH-C: CW001P03-SH-C:
2009/09/19 23:56 903 99.20851502
2009/09/20 00:11 900 99.21775174
2009/09/20 00:26 901 99.22421268
2009/09/20 00:41 899 99.22657119
2009/09/20 00:56 928 99.24876246
2009/09/20 01:12 872 99.25364643
2009/09/20 01:26 902 99.2604542
CW001P03-SH-E: CW001P03-SH-E:
2009/09/19 23:56 904 32.10759256
2009/09/20 00:11 899 32.10688525
2009/09/20 00:26 901 32.10960911
2009/09/20 00:41 900 32.10492405
2009/09/20 00:56 927 32.10638105
2009/09/20 01:12 872 32.09597414
2009/09/20 01:26 902 32.09691138
Thanks,

 

[Don Guillett]

One way
Sub AverageBlocks()
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(lr, 1) = "c"
With Range("a1:a" & lr)
' Set c = .Find(2, LookIn:=xlValues)
Set c = Columns("a").Find(What:="C", After:=Range("a1"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
fr = c.Row
nR = Columns("a").Find(What:="C", After:=Cells(fr, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row - 1
If nR = 1 Then
Cells(lr, 1) = " "
Exit Sub
End If
'MsgBox fr
'MsgBox nR
'MsgBox Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3)))
Cells(nR, 5) = .Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3)))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[Richard]

Hi Don,

Thanks a MILLION. After I added in the Dim statements your code worked like
a dream. FANTASTIC!!! Thanks again.