Finding date/time greater than & less than, then 'get value'

[TheRookie]

Sigh... without confusing everyone with my 'non-working' code, here's what I am attempting to do:
1) I have a WS named "Washer_S25", among many others.
2) Every sheet has a date/time column B; using the format MM/DD/YY HH:MM
2B) A userform1 posts the date & time using the Now function.
3) Using helper cells, I have set up a date window and I want to find entries within that window.
3B) The "BeginDate1" (WS Cell L8) is using the =Now function and subtracting 1 day, and setting the time to 6AM. The EndDate1 (WS, cell L9) uses the same NOW and sets the time to 6AM. Same format as above; basically a 24 hour window.
4) I am trying to get UserForm2 (commandbutton click) to find entries greater than and less than BeginDate and EndDate.

After hours, and hours (forums and searching). nothing works, and gives errors. Is this enough to ask for some help with the VB Userform2 code? Or should I post those embarrassing stumbles?

 

[mole999]

date and time are stored as integer and decimal i.e. 4352.5 so regardless of format you only need to get a value, does that help ?.. 6am is .25

 

[TheRookie]

Well, I got that far, for example 12/20/18 15:40 = [TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]43454.65[/TD]
[/TR]
</tbody>[/TABLE]
. Here's where I am stumbling (falling down).

Private Sub CommandButton4_Click()
'time for previous 24 hours. From 6am to 6am


Dim Ws As Worksheet
Dim BeginDate1 As Single
Dim EndDate1 As Single
Dim FirstDate1 As Single
Dim SecondDate1 As Single


BeginDate1 = Worksheets("Washer_S25").Range("L8")
EndDate1 = Worksheets("Washer_S25").Range("L9")


Set Ws = ActiveWorkbook.Worksheets("Washer_S25")


FirstDate1 = Application.WorksheetFunction.Match(BeginDate1, "B2:B", -1)


MsgBox "The Report begins at " & BeginDate1 & " ends at " & EndDate1


End Sub
==============
The data structure looks like this:
[TABLE="width: 192"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] A
STATUS[/TD]
[TD]B
Since[/TD]
[/TR]
[TR]
[TD]Install[/TD]
[TD="align: right"]10/1/18 14:00[/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]10/12/18 8:15[/TD]
[/TR]
[TR]
[TD]Engineering[/TD]
[TD="align: right"] 10/12/18 11:00[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]10/12/18 11:30[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]10/13/18 14:00[/TD]
[/TR]
[TR]
[TD]UM-Notif[/TD]
[TD="align: right"]10/13/18 23:00[/TD]
[/TR]
</tbody>[/TABLE]


Scary, huh?

 

[mole999]

i think your date should b Dim x as Date, not single

> https://excelmacromastery.com/vba-dim/ or don't declare it so Dim x and let excel decide on what way to use it

 

[TheRookie]

i think your date should b Dim x as Date, not single

> https://excelmacromastery.com/vba-dim/ or don't declare it so Dim x and let excel decide on what way to use it

I am an "over-analyzer!" Thanks for the tip. I remarked out the DIM statements and tried again. This time though, I get a new error "Unable to get Match property of Worksheet Function class.
Private Sub CommandButton4_Click()
'time for previous 24 hours. From 6am to 6am


Dim Ws As Worksheet
'Dim BeginDate1 As Single
'Dim EndDate1 As Single
'Dim FirstDate1 As Single
'Dim SecondDate1 As Single


BeginDate1 = Worksheets("Washer_S25").Range("L8")
EndDate1 = Worksheets("Washer_S25").Range("L9")


Set Ws = ActiveWorkbook.Worksheets("Washer_S25")


FirstDate1 = Application.WorksheetFunction.Match(BeginDate1, "B2:B", -1)


MsgBox "The Report begins at " & BeginDate1 & " ends at " & EndDate1




End Sub

 

[mole999]

at the top of the page place Option Explicit which should make sure you add the bits that aren't found

what are your expectations of


FirstDate1 = Application.WorksheetFunction.Match(BeginDate1, "B2:B", -1)

I think you are looking for the last cell in column B

 

[TheRookie]

Thank you for your help on this. Apologies for the abstract code. Actually, I am needing the "Status" from Column A. In this 24 hour window I set up, How much time was in each state, and then subtract that from 24 hours. That's all, and I've driven my train into a ditch.

Each time a status is changed, it is time stamped, and the total time in the previous state is calculated. For example "Qualify" below on 10/12 lasted for 2:45, then "Engineering" for another 30 minutes. In this example, I am only looking at the Date/Time for 10/12/18 06:00 through 10/13/18 06:00. Together, those two states lasted for 3:15. This would give me 24 - 3.25 for 20.75 (or other %'s like 100-(3.25/24)=86.5% Running.


[TABLE="width: 192"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]STATUS[/TD]
[TD]Since[/TD]
[/TR]
[TR]
[TD]Install[/TD]
[TD="align: right"]10/1/18 14:00[/TD]
[/TR]
[TR]
[TD]Qualify[/TD]
[TD="align: right"]10/12/18 8:15[/TD]
[/TR]
[TR]
[TD]Engineering[/TD]
[TD="align: right"]10/12/18 11:00[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]10/12/18 11:30[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]10/13/18 14:00[/TD]
[/TR]
</tbody>[/TABLE]

FirstDate1 = Application.WorksheetFunction.Match(BeginDate1, "B2:B", -1)
was an attempt to begin capturing the first encountered date in the 24 hour window. I had tried Lookup, VLookup, etc.

 

[mole999]

so match will return a value you specify, by its position in the range available as first so you might consider B:B or B2:B1000000

> https://support.office.com/en-us/article/MATCH-function-E8DFFD45-C762-47D6-BF89-533F4A37673A

not something i use much (no usual need)
Your Begin value might be = TODAY()-1+0.25 which If I have that right is 06:00 yesterday from todays value, but you need to maybe just use TODAY()-1

 

[MARK858]

If your dates are in order as you have posted then try...

Sub CellGrtrThan()
    Dim rng As Range, rng2nd As String
    Dim BeginDate1 As Double, rng1st As String
    Dim EndDate1 As Double, LastRow As Long
    Dim xRw As Long, cll As Range

    With Worksheets("Washer_S25")
        BeginDate1 = .Range("L8")
        EndDate1 = .Range("L9")

        Set rng = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)

        For Each cll In rng
            If cll.Value > BeginDate1 Then
                rng1st = cll.Address
                Exit For
            End If
        Next

        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        For xRw = LastRow To 2 Step -1
            If .Cells(xRw, "B") < EndDate1 Then
                rng2nd = .Cells(xRw, "B").Address
                Exit For
            End If
        Next
    End With

    MsgBox "The Report begins at " & rng1st & " ends at " & rng2nd
End Sub

 

[TheRookie]

MARK858! #1 in my book! This is exactly what I was trying to do!

It runs without error and provides the prompt of the cells found within the date range.

Instead of getting the rng1st and rng2nd "cll.address though, how can I get it to give each of the values in column A?

For example, this returned the msgBox "The report begins at $B$54 and ends at $B$61." If column A has the following values A54 - A61 as:

[TABLE="width: 81"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Running[/TD]
[/TR]
[TR]
[TD]Idle[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[/TR]
[TR]
[TD]UM-Notif[/TD]
[/TR]
[TR]
[TD]UM-Maint
UM-Comp
Qualify
Running[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Column C captures the time delta of each of these state changes, and that's where I will use the "State" and "Time on State". Ultimately, the Msg prompt would state something like this, "The Washer had an UP Time of 80%."

Of course, I will work on the code myself now that you've given me the starting point, but "MAN O MAN" this was a big help. If you have any more suggestions, I am all ears.