Time query to find number of times in a certain range

Hello all,

So I am trying to devise a code that will allow a user to input a range of times via dialog box. Then with this range (a startTime and endTime), the VBA code will search the worksheet and count the number of trips that take place in that time interval. I can't figure out a way to convert the user entry into a h:mm AM/PM format but I feel like I am on the right track.

If anyone could take a look at my attached code/file and steer me in the right direction, that would be awesome! I would also be happy to clarify anything I left out, thank you.

-Chad

Dim count1 as integer
Set pullinspullouts1 = Worksheets("Pull-ins & Pull-outs").Range("B4", Range("B" & Rows.Count).End(xlUp))

'Convert text to time
fromTime.NumberFormat = "h:mm AM/PM"
toTime.NumberFormat = "h:mm AM/PM"
        
End If

For Each cell In pullinspullouts1
    If InStr(cell.Text, "x") > 0 Then
        n = Len(Trim(cell))
        cellval = Left(Trim(cell), n - 5) & ":" & Left(Right((Trim(cell)), 4), 2) & " AM"
        cell.NumberFormat = "h:mm AM/PM"
    End If
    
    
    If cell.Value >= #fromTime# And cell.Value < #toTime# Then
            count1 = count1 + 1
    End If
    
    MsgBox "There are " & count1 & "trips from " & fromTime & "to " & toTime

timequery.xlsm

   If cell.Value >= #fromTime# And cell.Value < #toTime# Then

fromTime is not defined as a cell or range. You need to format it a cell or range first before you test it with if.

count1 = 0
For Each cell In worktotalDXdept1
cell.NumberFormat = "h:mm AM/PM"
    If InStr(cell.Text, "x") > 0 Then
        n = Len(Trim(cell))
        cellval = Left(Trim(cell), n - 5) & ":" & Left(Right((Trim(cell)), 4), 2) & " AM"
        If cellval >= fromTime And cellval < toTime Then
            count1 = count1 + 1
        End If
    End If
    
    If Not InStr(cell.Text, "x") > 0 Then
        If cell.Text >= fromTime And cell.Text < toTime Then
            count1 = count1 + 1
        End If
    End If
Next

    MsgBox "There are " & count1 & " trips from " & fromTime & " to " & toTime & " ."

This code works for one column of times, however when I enter a range of times outside the specified row, count1 always returns "0" trips. How can I have it search multiple columns of time?

May be this line if worktotalDXdept1 is a named range

For Each cell In worktotalDXdept1

Sorry!
I can not test it, but you need to remove the hashes from these lines

 If cellval >= Range("A2000").Value And cellval <= Range("A2000").Value Then

You may putin dates in hashes but not any other.