VBA Syntax help for a Loop

I need some VBA syntax help. I have a table (see attached spreadsheet) that I need to loop through and get the S_DT (starting time) when an alarm_count goes over 10 and save that date/time as a variable, then continue until the alarm_count goes below 5, get the E_DT (Ending Time) and save that date/time as a different variable, then write those two variables to a table. Then continue on to the next greater than ten and do the same until End of File.
Alarm_Data_Alb_10.xlsx
dgravittAsked:
Who is Participating?
 
aikimarkCommented:
This routine:
Public Sub Q_29095423()
    Dim rs As Recordset
    Dim dtStart As Date
    Set rs = DBEngine(0)(0).OpenRecordset("Alarm_Data_Alb_10", dbOpenDynaset)
    rs.FindFirst "Alarm_Count>10"
    If rs.NoMatch Then
        MsgBox "no starting rows found"
        Exit Sub
    End If
    Do
        dtStart = rs!S_DT
        rs.FindNext "Alarm_Count<5"
        If rs.NoMatch Then
            MsgBox "unmatched stoping row for " & dtStart
            Exit Do
        End If
        Debug.Print dtStart, rs!E_DT
        rs.FindNext "Alarm_Count>10"
    Loop Until rs.NoMatch
End Sub

Open in new window

Produces the following result in the immediate window:
2/1/2018 9:00:00 AM         2/1/2018 9:39:00 AM 
2/1/2018 11:30:00 PM        2/1/2018 11:59:00 PM 
2/2/2018 12:30:00 AM        2/2/2018 12:49:00 AM 
2/2/2018 8:00:00 AM         2/2/2018 8:19:00 AM 
2/2/2018 11:00:00 AM        2/2/2018 11:39:00 AM 
2/2/2018 11:40:00 PM        2/3/2018 12:09:00 AM 
2/3/2018 10:10:00 PM        2/3/2018 10:29:00 PM 
2/3/2018 11:00:00 PM        2/3/2018 11:29:00 PM 
2/4/2018 8:50:00 AM         2/4/2018 9:59:00 AM 
2/4/2018 11:00:00 PM        2/4/2018 11:39:00 PM 
2/5/2018 5:50:00 AM         2/5/2018 6:09:00 AM 
2/5/2018 9:30:00 AM         2/5/2018 10:19:00 AM 
2/5/2018 12:50:00 PM        2/5/2018 1:09:00 PM 
2/5/2018 2:40:00 PM         2/5/2018 2:59:00 PM 
2/5/2018 8:50:00 PM         2/5/2018 9:09:00 PM 
2/5/2018 11:10:00 PM        2/5/2018 11:39:00 PM 
2/6/2018 9:40:00 AM         2/6/2018 10:09:00 AM 
2/6/2018 10:20:00 AM        2/6/2018 11:09:00 AM 
2/6/2018 4:10:00 PM         2/6/2018 4:49:00 PM 
2/6/2018 11:10:00 PM        2/6/2018 11:39:00 PM 
2/7/2018 11:20:00 AM        2/7/2018 11:59:00 AM 
2/7/2018 11:10:00 PM        2/8/2018 12:09:00 AM 
2/8/2018 10:20:00 AM        2/8/2018 11:29:00 AM 
2/8/2018 2:50:00 PM         2/8/2018 3:09:00 PM 
2/8/2018 11:20:00 PM        2/9/2018 12:09:00 AM 
2/9/2018 9:00:00 AM         2/9/2018 9:39:00 AM 
2/9/2018 10:20:00 AM        2/9/2018 10:39:00 AM 
2/9/2018 12:20:00 PM        2/9/2018 12:39:00 PM 
2/9/2018 11:40:00 PM        2/10/2018 12:09:00 AM 
2/10/2018 11:00:00 PM       2/10/2018 11:49:00 PM 
2/10/2018 11:50:00 PM       2/11/2018 12:09:00 AM 
2/11/2018 12:30:00 AM       2/11/2018 1:19:00 AM 
2/11/2018 11:00:00 PM       2/11/2018 11:59:00 PM 
2/12/2018 11:10:00 PM       2/12/2018 11:49:00 PM 
2/13/2018 12:30:00 PM       2/13/2018 12:49:00 PM 
2/13/2018 1:00:00 PM        2/13/2018 1:19:00 PM 
2/13/2018 11:10:00 PM       2/13/2018 11:39:00 PM 
2/14/2018 10:50:00 AM       2/14/2018 11:29:00 AM 
2/14/2018 7:20:00 PM        2/14/2018 7:39:00 PM 
2/14/2018 11:00:00 PM       2/14/2018 11:39:00 PM 
2/15/2018 12:30:00 PM       2/15/2018 1:09:00 PM 
2/15/2018 11:20:00 PM       2/15/2018 11:49:00 PM 
2/16/2018 11:30:00 PM       2/17/2018 12:19:00 AM 
2/17/2018 2:40:00 AM        2/17/2018 3:09:00 AM 
2/17/2018 10:30:00 PM       2/17/2018 10:49:00 PM 
2/17/2018 11:00:00 PM       2/17/2018 11:39:00 PM 
2/18/2018 11:00:00 PM       2/18/2018 11:39:00 PM 
2/19/2018 11:00:00 PM       2/20/2018 12:09:00 AM 
2/20/2018 11:20:00 AM       2/20/2018 12:29:00 PM 
2/20/2018 12:50:00 PM       2/20/2018 1:09:00 PM 
2/21/2018 12:50:00 PM       2/21/2018 1:39:00 PM 
2/22/2018 3:20:00 AM        2/22/2018 3:59:00 AM 
2/22/2018 9:00:00 AM        2/22/2018 9:39:00 AM 
2/23/2018 3:30:00 AM        2/23/2018 3:59:00 AM 
2/24/2018 1:30:00 AM        2/24/2018 1:59:00 AM 
2/24/2018 3:30:00 AM        2/24/2018 4:09:00 AM 
2/24/2018 11:00:00 PM       2/24/2018 11:29:00 PM 
2/24/2018 11:50:00 PM       2/25/2018 12:29:00 AM 
2/25/2018 11:00:00 PM       2/25/2018 11:39:00 PM 
2/26/2018 11:10:00 AM       2/26/2018 11:39:00 AM 
2/26/2018 7:00:00 PM        2/26/2018 7:29:00 PM 
2/27/2018 12:20:00 PM       2/27/2018 1:29:00 PM 
2/27/2018 2:00:00 PM        2/27/2018 4:59:00 PM 
2/27/2018 7:10:00 PM        2/27/2018 7:39:00 PM 
2/28/2018 6:40:00 AM        2/28/2018 6:59:00 AM 
2/28/2018 9:50:00 AM        2/28/2018 11:09:00 AM 
2/28/2018 12:10:00 PM       2/28/2018 1:09:00 PM 
2/28/2018 7:10:00 PM        2/28/2018 7:49:00 PM 

Open in new window

0
 
aikimarkCommented:
goes over 10 and save that date/time as a variable, then continue until the alarm_count goes below 5
>=10 or >10?
<=5 or <5?
0
 
dgravittAuthor Commented:
>10 and <5
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
What should happen when the next <5 row is below one or more >10 rows?
0
 
dgravittAuthor Commented:
Working in ascending order, go until you find the first value over 10, record the start time for that record, then continue until the alarm_count value drops below 5, record the end time value for that record. Write the start time of the first and the end time of the last and write to another table, that gives the duration. start over after the last record where you recorded the end date. It doesn't matter how many records. Thanks for looking at this.
0
 
aikimarkCommented:
Sub Q_29095423()
    Dim wks As Worksheet
    Dim wksStartStop As Worksheet
    Dim rng As Range
    Dim rngStartStop As Range
    Dim rngArea As Range
    Dim lngRow As Long
    
    Set wksStartStop = Worksheets("StartStop")
    Set rngStartStop = wksStartStop.Cells(1, 1)
    Set wks = Sheets(1)
    lngRow = wks.UsedRange.Rows.Count
    Set rng = wks.UsedRange.Offset(1).Resize(lngRow - 1)
    rng.AutoFilter 3, ">10"
    'wks.UsedRange.AutoFilter 3, ">10"
    For Each rngArea In rng.SpecialCells(xlCellTypeVisible).Areas   'wks.UsedRange.SpecialCells(xlCellTypeVisible).Areas
        lngRow = rngArea.Row + 1
        Do
            If wks.Cells(lngRow, 3).Value < 5 Then
                'Debug.Print rngArea.Cells(1, 1).Value, wks.Cells(lngRow, 2).Value, rngArea.Row, lngRow
                rngStartStop.Resize(1, 4).Value = Array(rngArea.Cells(1, 1).Value, wks.Cells(lngRow, 2).Value, rngArea.Row, lngRow)
                Set rngStartStop = rngStartStop.Offset(1)
                Exit Do
            End If
            lngRow = lngRow + 1
        Loop Until lngRow > wks.UsedRange.Rows.Count
    Next
End Sub

Open in new window

Produced the following in the StartStop worksheet.  The column headers were manually added for clarity.
S_DT        	E_DT        	S_DTrow	E_DTrow
2/1/2018 9:00	2/1/2018 9:39	56	59
2/1/2018 23:30	2/1/2018 23:59	143	145
2/2/2018 0:30	2/2/2018 0:49	149	150
2/2/2018 8:00	2/2/2018 8:19	194	195
2/2/2018 11:00	2/2/2018 11:39	212	215
2/2/2018 23:40	2/3/2018 0:09	288	290
2/3/2018 22:10	2/3/2018 22:29	423	424
2/3/2018 23:00	2/3/2018 23:29	428	430
2/4/2018 8:50	2/4/2018 9:59	487	493
2/4/2018 9:30	2/4/2018 9:59	491	493
2/4/2018 23:00	2/4/2018 23:39	572	575
2/5/2018 5:50	2/5/2018 6:09	613	614
2/5/2018 9:30	2/5/2018 10:19	635	639
2/5/2018 9:50	2/5/2018 10:19	637	639
2/5/2018 12:50	2/5/2018 13:09	655	656
2/5/2018 14:40	2/5/2018 14:59	666	667
2/5/2018 20:50	2/5/2018 21:09	703	704
2/5/2018 23:10	2/5/2018 23:39	717	719
2/6/2018 9:40	2/6/2018 10:09	780	782
2/6/2018 10:20	2/6/2018 11:09	784	788
2/6/2018 10:40	2/6/2018 11:09	786	788
2/6/2018 16:10	2/6/2018 16:49	819	822
2/6/2018 23:10	2/6/2018 23:39	861	863
2/7/2018 11:20	2/7/2018 11:59	934	937
2/7/2018 23:10	2/8/2018 0:09	1005	1010
2/8/2018 10:20	2/8/2018 11:29	1072	1078
2/8/2018 14:50	2/8/2018 15:09	1099	1100
2/8/2018 23:20	2/9/2018 0:09	1150	1154
2/9/2018 9:00	2/9/2018 9:39	1208	1211
2/9/2018 10:20	2/9/2018 10:39	1216	1217
2/9/2018 12:20	2/9/2018 12:39	1228	1229
2/9/2018 23:40	2/10/2018 0:09	1296	1298
2/10/2018 23:00	2/10/2018 23:49	1436	1440
2/10/2018 23:50	2/11/2018 0:09	1441	1442
2/11/2018 0:30	2/11/2018 1:19	1445	1449
2/11/2018 23:00	2/11/2018 23:59	1580	1585
2/12/2018 23:10	2/12/2018 23:49	1725	1728
2/13/2018 12:30	2/13/2018 12:49	1805	1806
2/13/2018 13:00	2/13/2018 13:19	1808	1809
2/13/2018 23:10	2/13/2018 23:39	1869	1871
2/14/2018 10:50	2/14/2018 11:29	1939	1942
2/14/2018 19:20	2/14/2018 19:39	1990	1991
2/14/2018 23:00	2/14/2018 23:39	2012	2015
2/14/2018 23:20	2/14/2018 23:39	2014	2015
2/15/2018 12:30	2/15/2018 13:09	2093	2096
2/15/2018 23:20	2/15/2018 23:49	2158	2160
2/16/2018 23:30	2/17/2018 0:19	2303	2307
2/17/2018 2:40	2/17/2018 3:09	2322	2324
2/17/2018 22:30	2/17/2018 22:49	2441	2442
2/17/2018 23:00	2/17/2018 23:39	2444	2447
2/18/2018 23:00	2/18/2018 23:39	2588	2591
2/19/2018 23:00	2/20/2018 0:09	2732	2738
2/20/2018 11:20	2/20/2018 12:29	2806	2812
2/20/2018 12:10	2/20/2018 12:29	2811	2812
2/20/2018 12:50	2/20/2018 13:09	2815	2816
2/21/2018 12:50	2/21/2018 13:39	2959	2963
2/22/2018 3:20	2/22/2018 3:59	3046	3049
2/22/2018 9:00	2/22/2018 9:39	3080	3083
2/22/2018 9:20	2/22/2018 9:39	3082	3083
2/23/2018 3:30	2/23/2018 3:59	3191	3193
2/24/2018 1:30	2/24/2018 1:59	3323	3325
2/24/2018 3:30	2/24/2018 4:09	3335	3338
2/24/2018 23:00	2/24/2018 23:29	3452	3454
2/24/2018 23:50	2/25/2018 0:29	3457	3460
2/25/2018 23:00	2/25/2018 23:39	3596	3599
2/26/2018 11:10	2/26/2018 11:39	3669	3671
2/26/2018 19:00	2/26/2018 19:29	3716	3718
2/27/2018 12:20	2/27/2018 13:29	3820	3826
2/27/2018 13:10	2/27/2018 13:29	3825	3826
2/27/2018 14:00	2/27/2018 16:59	3830	3847
2/27/2018 14:20	2/27/2018 16:59	3832	3847
2/27/2018 14:40	2/27/2018 16:59	3834	3847
2/27/2018 19:10	2/27/2018 19:39	3861	3863
2/28/2018 6:40	2/28/2018 6:59	3930	3931
2/28/2018 9:50	2/28/2018 11:09	3949	3956
2/28/2018 10:10	2/28/2018 11:09	3951	3956
2/28/2018 12:10	2/28/2018 13:09	3963	3968
2/28/2018 19:10	2/28/2018 19:49	4005	4008

Open in new window

0
 
dgravittAuthor Commented:
Sorry, in my original question, I stated "I have a table". The spreadsheet is just a view of that table. I am working in Access not Excel. I'm sorry, but I don't understand your post. My apologies if I wasn't clear of my need for VBA module to use in Access.
0
 
aikimarkCommented:
Ah.  Missed that detail.
0
 
aikimarkCommented:
Is there an autonumber column in the table?
0
 
dgravittAuthor Commented:
No problem, thanks for taking the time
0
 
dgravittAuthor Commented:
no, it will be like the table shown without the alarm_count field
0
 
aikimarkCommented:
Just as I posted this query:
SELECT STA.[S_DT] As AlarmStart, 
(SELECT Min(STO.S_DT) 
FROM Alarm_Data_Alb_10 As STO
WHERE (STO.[Alarm_Count] < 5
	And STO.[E_DT] > STA.[S_DT]
	)
) As AlarmEnd
FROM Alarm_Data_Alb_10 As STA
WHERE (STA.[Alarm_Count] > 10)

Open in new window

I noticed your comment.
it will be like the table shown without the alarm_count field
You need to explain this ASAP.  Without the alarm_count field nothing can be done.  Your stated requirement depends on the existence of the alarm_count field.
0
 
dgravittAuthor Commented:
I was referring to the table the data is being written to once the Start and End times are determined. But, there is no auto increment field in either table. What I'm trying to determine is what is referred to as a flood of alarms. It begins when the alarm count reached 10 and ends when it falls back below 5. I need to know how long each duration of the flood lasts.
0
 
dgravittAuthor Commented:
That looks good. let me work with it today.
0
 
dgravittAuthor Commented:
This worked as I needed. Totally different process than what I was trying to do. Glad I asked for another set of eyes to look at it. Thank You!
0
 
aikimarkCommented:
Did you mean to accept the comment that you selected?  I would think my later comment is the actual solution.
0
 
dgravittAuthor Commented:
you're right. Not sure how to change it
0
 
dgravittAuthor Commented:
Thanks, I have changed my selection. I appreciate all the help.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.