[Excel VBA] Filter records within the time range

[colintis]

Hi guys, I'm actually doing this from Access, which requires to connect to an excel worksheet, then calculate the average number of that day within a specific time.

For testing purpose I put the code into excel, and here is the code I had written and tested so far.

    'RowLoc is data source row number, SheetRow is the output row number
    Dim RowLoc As Integer, SheetRow As Integer
    Dim TimeStart As String, TimeEnd As String
    Dim StackDate As String
    Dim StackTime As String

    'StackStart and StackEnd records the row number
    Dim StackStart As Integer, StackEnd As Integer
    Dim Started As Boolean, Ended As Boolean
    Dim EndDate As String    'EOF
    
    RowLoc = 3
    SheetRow = 2
    TimeStart = Format(TimeValue("06:00:00"), "hh:mm:ss AMPM")
    TimeEnd = Format(TimeValue("11:00:00"), "hh:mm:ss AMPM")
    EndDate = Format(E6Stat.Range("A65536").End(xlUp), "Long Date")
    
    Do
        StackDate = E6Stat.Cells(RowLoc, "A")
        
        If StackDate = "" Then
            Exit Do
        Else
            Do While StackDate = E6Stat.Cells(RowLoc, "A")
            
                StackTime = Format(E6Stat.Cells(RowLoc, "B"), "hh:mm:ss AMPM")
                
                If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
                    StackStart = RowLoc
                    Started = True
                    
                ElseIf StackTime > TimeEnd And Started And Not Ended Then
                
                    StackEnd = RowLoc - 1
                    Ended = True
                    
                End If
                
                RowLoc = RowLoc + 1
                
            Loop
            
        End If
        
        'Add the gather informtion to the sheet before moving on
        E6Avg.Cells(SheetRow, "A") = Format(StackDate, "Long Date")
        E6Avg.Cells(SheetRow, "B") = "6:00 AM - 11:00AM"
        E6Avg.Cells(SheetRow, "C").Formula = "=AVERAGE('E6 - Sick'!M" & StackStart & ":M" & StackEnd & ")"
        E6Avg.Cells(SheetRow, "D") = StackStart
        E6Avg.Cells(SheetRow, "E") = StackEnd
        E6Avg.Cells(SheetRow, "F") = StackTime
        E6Avg.Cells(SheetRow, "G") = TimeStart
        E6Avg.Cells(SheetRow, "H") = TimeEnd
        
        SheetRow = SheetRow + 1
        
        Started = False
        Ended = False

    Loop While StackDate <> ""

The output for column D to H is to check the value when the result is made.

Currently within the code, the time ranges do not determine if the time from column B is AM or PM. Anyone who knows how to correct this error? Many thanks.

[colintis]

I found the answer with the InStr function, and this is what I've done by placing a IF statement to the inner IF that compares the time.

If InStr(8, StackTime, "AM") > 0 Then
    If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
        StackStart = RowLoc
        Started = True
        
    ElseIf StackTime > TimeEnd And Started And Not Ended Then
    
        StackEnd = RowLoc - 1
        Ended = True
        
    End If
End If

If there's any better logic than this one to improve performance, please let me know.