looping thru rows of variable length

[gtslabs]

I used vba to import a tab delimited text file.
I need to automatically do calcs on the data.
My A column has data in variable lengths in the following format
(beginning with Cell A22):

[Step 1]
time
12:00
12:02
12:04
12:06

[Step 2]
time
13:00
13:02
13:04
13:06
13:08

[Step X] Variable amount of steps and data
time
13:00
13:02
13:04
13:06


I want to put in a column the elapsed time for the data in each step,
restarting the time elapsed at 0 for each step.
So the first value would have 0. So in K24 I would have 0, in K25 I
would have A25-$A$24.
I am not sure how to loop and test to see if I need to start over
again with the next [Step]
I assume I need to anchor the first time reading after the word "time"
then use that to subtract from each other value untile a blank row is
found. Then start over.

 

[Per Jessen]

Hi

This should do it, just remember to format column K as time to show results
correct.

Sub test()
Dim StepStart As Range
Dim off As Long
Set StepStart = Range("A22")
off = 2

Do
If StepStart.Offset(off, 0).Value <> "" Then
If Not IsNumeric(StepStart.Offset(off - 1).Value) Then
StepStart.Offset(off, 10) = 0
Else
StepStart.Offset(off, 10).Value = StepStart.Offset(off,
0).Value - _
StepStart.Offset(off - 1, 0).Value
End If
off = off + 1
Else
Set StepStart = StepStart.Offset(off + 2)
off = 2
End If
Loop Until StepStart.Value = ""
End Sub

Regards,
Per

 

[mudraker]

Per Jessen code will notlop unless there is a value in A22

Try this macro

Sub CalcTime()
Dim lLR As Long
Dim l4Row As Long
Dim rStart As Range

lLR = Cells(Rows.Count, "a").End(xlUp).Row

For l4Row = 1 To lLR Step 1
If IsNumeric(Cells(l4Row, "a").Value) And Not Cells(l4Row, "a") =
"" Then
If rStart Is Nothing Then
Set rStart = Cells(l4Row, "a")
End If
With Cells(l4Row, "b")
..Value = Cells(l4Row, "a").Value - rStart.Value
..NumberFormat = "h:mm"
End With
Else
Set rStart = Nothing
End If
Next l4Row
End Sub


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com