If Criteria Wrong Results

[Ark68]

This line of code:

If Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) Then

Appears to be successfully triggering when rounded svc_off (time) variable falls within a time range defined by rounded crw_st and rounded crw_ed.
From a loop of 12 rows of data each with different values of crw_st and crw_ed, 6 pass the criteria. (svc_off falls between the defined time range for that row)

The remaining 6 do not. I am trying to trigger code when the opposite of above occurs ... the svc_off is not in the rame range. So, I tried this code:

If Not Round(svc_off, 3) >= Round(crw_st, 3) And Not Round(svc_off, 3) <= Round(crw_ed, 3) Then

However, I must have something out of place as it's not triggering when svc_off isn't in the time range.

 

[jasonb75]

It would be easier to use an Else line with the original If rather than trying to reverse the logic.

There are four possible combinations of results, the 'remaining 6' probably fall into one of the two combinations that you haven't allowed for.

Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) Original code
Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) > Round(crw_ed, 3)
Round(svc_off, 3) < Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3)
Round(svc_off, 3) < Round(crw_st, 3) And Round(svc_off, 3) > Round(crw_ed, 3) What your second attempt looks for.
The 2 in the middle have been missed out.

 

[joeu2004]

@Ark68 .... Is there some reason why you don't just write:

If Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) Then
    '.... do something
Else
    '.... do something else
End If

Be that as it may, the "opposite" of "A and B" is "not A __or__ not B". And the opposite of "x >= y" is "x < y". And the opposite of "x <= z" is "x > z".

So the "opposite" of your If-statement is:

If Round(svc_off, 3) < Round(crw_st, 3) Or Round(svc_off, 3) > Round(crw_ed, 3) Then

-----

In math, we write y <= x <= z for "in between". We cannot write that in VBA. But we can write something similar, which might make things clearer:

If Round(crw_st, 3) <= Round(svc_off, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) Then

Then the "opposite" might be clearer, too:

If Round(svc_off, 3) < Round(crw_st, 3) Or Round(crw_ed, 3) < Round(svc_off, 3) Then

 

[Ark68]

Thank you all for your support. My apologies for taking so long to acknowledge your generous contribution.
I agree 100% that the simple solution would be to utilize an Else condition, but for what I'm trying to do, I couldn't wrap my head around how to integrate that. I made this post that, likely because of poor explanation, didn't receive any replys.

In a nutshell, I need to create two lists - one with which the time falls within the service range - captured by the the working IF statement, and a second list of those that do not (what I'm trying to resolve now). The two lists, separated by a separator ("-----") are appended to create one list that forms the validation list for a cell.

My approach is to create both lists separately and join them. With using the Else statement, I would need to figure out how to create the second list below the separator while still ensuring the list section could continue to grow. (the lists are created by looping through a series of values, and these values are put in one of the two lists)

Here is the actual code of what I have accomplished so far, but am unable to get the right results of the "second list"

'2.  Create List validation for cell
        Stop
        .Range("I2:I20").Clear
        irw = 2
        lrw = ws_thold.Cells(ws_thold.Rows.Count, "A").End(xlUp).Row
        Set rng_stf_schd = ws_thold.Range("A2:E" & lrw)
       
        'valid crew list (section 1 of 2 - appropriate)
        For L2 = 2 To lrw
            shift = ws_thold.Cells(L2, 1)
            crw_st = bkg_date + ws_thold.Cells(L2, 4)
            crw_ed = bkg_date + ws_thold.Cells(L2, 5)
            If crw_ed < crw_st Then crw_ed = bkg_date + 1 + crw_ed
            svc_off = bkg_dst + TimeValue("00:45")
            If Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) Then
                ws_thold.Cells(irw, 9) = shift
                irw = irw + 1
            End If
        Next L2
       
        'spacer
        ws_thold.Cells(irw, 9).Value = "NA"
        irw = irw + 1
        ws_thold.Cells(irw, 9).Value = "NR"
        irw = irw + 1
        ws_thold.Cells(irw, 9) = "-----"
        irw = irw + 1
       
        'invalid crew list (section 2 of 2 - inappropriate)
        For L2 = 2 To lrw
            'shift = ws_thold.Cells(L2, 1)
            'crw_st = ws_thold.Cells(L2, 4)
            'crw_ed = bkg_date + ws_thold.Cells(L2, 5)
            'If crw_ed < crw_st Then crw_ed = bkg_date + 1 + crw_ed
            If Not Round(svc_off, 3) >= Round(crw_st, 3) And Not Round(svc_off, 3) <= Round(crw_ed, 3) Then
                ws_thold.Cells(irw, 9) = crew
                irw = irw + 1
            End If
        Next L2
    
        '.Range("I2:I" & flcnt + 1).Value = .Range("A2:A" & flcnt + 1).Value
       
        Set rng_dsr_sig = .Range("I2:I" & irw)
        ThisWorkbook.Names.Add Name:="nr_dsr_sig", RefersTo:=rng_dsr_sig
   
    End With

So, I either need to figure out the code for the Else component to build an individual staggered dynamic list, or correct my If statement in an effort to create a second list of opposites to be joined to create the one list.

 

[Ark68]

I took another, more simple approach based on the joining of two lists. I used different criteria and appear to have it working.
Thanks once again all!

 

[joeu2004]

I took another, more simple approach based on the joining of two lists. I used different criteria and appear to have it working.

Okay. But for future note....

If Not Round(svc_off, 3) >= Round(crw_st, 3) And Not Round(svc_off, 3) <= Round(crw_ed, 3) Then

I already answered that. You would replace that line with the following:

If Round(svc_off, 3) < Round(crw_st, 3) Or Round(svc_off, 3) > Round(crw_ed, 3) Then

Alternatively:

If Not ( Round(svc_off, 3) >= Round(crw_st, 3) And Round(svc_off, 3) <= Round(crw_ed, 3) ) Then

Note that the expression between Not(...) is your first expression -- the "non-opposite" form.