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

Thanks,
Mark

--
Message posted via http://www.officekb.com

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

Thank You,

--
Message posted via http://www.officekb.com

It is very close, but it only gives me one result. Im looking to have it
continue down and average as may as it can. It could just be the way that the
data is stored. I can send you the file if it would help.

Thanks, Mark

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

I have altered it a little to make it so that it puts the formula in the
first empty cell in column L. Im stll having troubble trying to make it loop
through and find all of the data that meets the ,ten minute and only uses the
data once restriction.

Sub macro3()
Dim top 'first cell
Dim tlength 'difference between top and top + 60
Dim tstart 'starting time
Dim Boto
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
Boto = Cells(top + 60, 1).Select 'grab 60 cells later
tlength = Boto - tstart 'calculate time diff

If tlength < 0.006956018 Then 'for some reason this is greater than 10min
Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Activate 'first empty 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

--
Message posted via http://www.officekb.com

I would change your line " Boto = Cells(top + 60, 1).Select 'grab 60 cells
later

to "Boto = Cells(top + 60, 1).Value 'grab 60 cells later"

when I run your macro Boto = True instead of a real number. (might work for
you if you have a different edition of XL, I suppose)

I would change "top = 65000 " to "top = top + 60" so it continues with the
next 10 min of data.

I suppose you can change the while top < 64000 line to something else like
while(tlength 0)
because if the bottom cell is null or zero, you will get a negative value.
I noticed it will average the last bit of data once it reaches the empty
cells, so you may have less than 60 cells in the last average in the L
column. I am sure you can add another if statement to avoid that.




I just tested this, it rips right through the time column and spits out
averages of 60 cells of E into the L column.




"mrc1986 via OfficeKB.com" wrote:

I have altered it a little to make it so that it puts the formula in the
first empty cell in column L. Im stll having troubble trying to make it loop
through and find all of the data that meets the ,ten minute and only uses the
data once restriction.

Sub macro3()
Dim top 'first cell
Dim tlength 'difference between top and top + 60
Dim tstart 'starting time
Dim Boto
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
Boto = Cells(top + 60, 1).Select 'grab 60 cells later
tlength = Boto - tstart 'calculate time diff

If tlength < 0.006956018 Then 'for some reason this is greater than 10min
Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Activate 'first empty 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

--
Message posted via http://www.officekb.com