sloppy but works, I couldn't figure out how to evalute the difference between
cells in the time format. So i experimented with an extra 1 second to see
the breaking point as a decimal value.
This assumes that time rows are not left blank and if there is a break in
data, it is only seen in the time column as a large difference between one
time and the previous time. This also assumes that if the difference is
greater than 00:10:01 (meaning a time period within with no recording) then
move on. It is assuming that there is never a lower time difference between
rows than 10 secs.
good luck.



Sub macro3()
Dim top 'first cell
Dim tlength 'difference between top and top + 60
Dim tstart 'starting time

top = 1

While top < 64000 ' give it a way out of loop, adjust to your sheet lower if
you have smaller files.

Cells(top, 1).Select 'grab first cell
tstart = ActiveCell.Value
Cells(top + 60, 1).Select 'grab 60 cells later
tlength = ActiveCell.Value - tstart 'calculate time diff



If tlength < 0.006956018 Then 'for some reason this is greater than 10min
Range("l1").Select 'first cell in L
Selection.Formula = "=average(e" & top & ":e" & (top + 60) & ")" 'string to
put in l1 as formula
top = 65000 'exit strategy
End If
top = top + 1 'increment row number.
Wend

end sub

"mrc1986 via OfficeKB.com" wrote:

I dont know if it is possible to do this but I hope it is. I have about 3
years worth of excel files that takes a data collection every ten seconds.
In column A is the time that it takes the data. Sometimes there is a skip in
the data for various reasons. Column A is formatted to show in HH:MM:SS

I am looking at the value of range A1:A61 and see if there is exactally ten
minutes of data in that range.

If there is ten minutes of data in the I need it to range average E1:E61 in
like the first blank cell in column L.

If the value of range A1:A61 does not contain ten minutes of data I need it
to move down to A2:A62 and look for the ten minutes of data. And keep Moving
down one cell until it finds ten minutes of data.

Now to make it really complicated
Move down to 60 rows from the last cell that it just used from the above
steps in column A and start over.

Your Help is very appreciated

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200608/1