"AND" parameter for CASE STATEMENT??? WHEN, AND, THEN

[Sphinx404]

Sub Request2()


'THIS SUB: finds NONHAZ, UN2911, or UN3316 and if found populates No Action, No Error, or Compliant in columns AX:AZ


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim mycell As Range
Dim mycell2 As Range
Dim myLastRow As Long
Dim myworksheet As Worksheet


Set myworksheet = Worksheets("HazShipper")
myLastRow = myworksheet.Cells(myworksheet.Rows.Count, "A").End(xlUp).row


For Each mycell In Range("C2", Range("C" & Rows.Count).End(xlUp))


Select Case True
    Case UCase(mycell.Value) Like "*NONHAZ*" Or mycell.Value Like "*UN2911*" Or mycell.Value Like "*UN3316*" [B][COLOR=#ff0000]and LEN(U2)=8 and W2 = "TRUE"[/COLOR][/B]
            mycell.Offset(, 47).Value = "No Action"
            mycell.Offset(, 48).Value = "No Error"
            mycell.Offset(, 49).Value = "Compliant"
                                    
End Select


Next mycell


End Sub

The code works fine. But when trying to add-in the highlighted RED parts I'm confusing myself. Do I need to create separate variables for LEN (U2) and the W2 values?

I don't know if there is a way to add "AND" to a CASE statement. I've read about using WHEN and AND which would go something like:
CASE WHEN var1 = "TRUE" AND var2 = "UN2911" THEN

but the article was referring to SQL

 

[Joe4]

I have never seen CASE statements used like that in Excel VBA, and do not know if it is possible.
Typically, the initial "SELECT CASE" command references one cell or variable, and then you list the different values it may be under it.
I don't think you can check two different cells/variables like that (so I don't think you can introduce U2 like that).
I would recommend adding an IF statement under the CASE statement, like shown here: https://stackoverflow.com/questions/21319788/vba-case-select-multiple-conditions

 

[Tetra201]

The "Select Case True" is a powerful and rarely used construct (for example, see discussion here); it can handle both Or's and And's.

For the sub in Post # 1:
- do you really need a "Select Case" for just one case? -- Wouldn't an "If" suffice?
- are U2 and W2 variables or cells?
- is the "TRUE" a text string or a Boolean value?

 

[Sphinx404]

You know the feeling of being on the precipice of the knowledge you need and you just can't seem to connect the dots. I feel like I am in a constant state of that every time I go back to VBA.

You would laugh if you saw what I'm doing in my macros...
-Yes an IF statement would work better, and far easier but I'm having trouble setting up the logic correctly.
-U2 and W2 are just populated cells with report data stretching all the way to "myLastRow".
-Yes and no for boonleans. Some are, but this is the laughable part... The code I'm writing will only work for boonleans, but I have a mix of boonleans and text, which makes this ridiculous. So I'm adding in functions that copy the entire row and simply paste it back onto itself as text. I feel like tossing my computer across the room, but this is the fun part of macros.... learning.

I can explain the entire mess and I'm sure you guys can put it together in a matter of minutes... but this is going to take me a few days.

A link to what I have left to add to the macro:

https://drive.google.com/open?id=1KVhluM4VPFXhU_UmLLjN21kh721JxG2e

 

[Sphinx404]

This code seems to work for the first request in that link, so I'm just left with the second request.... the one where many parameters must be met.

Sub Request212121()


'THIS SUB: finds NONHAZ, UN2911, or UN3316 and if found populates No Action, No Error, or Compliant in columns AX:AZ


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim mycell As Range
Dim mycell2 As Range
Dim mycell3 As Range
Dim mycell4 As Range
Dim myLastRow As Long
Dim myworksheet As Worksheet


Set myworksheet = Worksheets("HazShipper")
myLastRow = myworksheet.Cells(myworksheet.Rows.Count, "A").End(xlUp).row


For Each mycell In Range("C2", Range("C" & myLastRow))


Select Case True
    Case UCase(mycell.Value) Like "*NONHAZ*" Or mycell.Value Like "*UN2911*" Or mycell.Value Like "*UN3316*"
            mycell.Offset(, 50).Value = "TRUE"
    Case Else
            mycell.Offset(, 50).Value = "FALSE"
    End Select
Next mycell


For Each mycell2 In Range("U2", Range("U" & myLastRow))
If Len(mycell2.Value) = 8 Then
    mycell2.Offset(, 33).Value = "TRUE"
Else
    mycell2.Offset(, 33).Value = "FALSE"
End If
Next mycell2
'CUT AND TRIM COLUMN W SO THE FUNCTION SEES IT AS TEXT NOT BOONLEAN
    Columns("X:X").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("W1").Select
    Selection.Copy
    Range("X1").Select
    ActiveSheet.Paste
    Range("X2").Formula = "=TRIM(CLEAN(W2))"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X" & myLastRow)
    Columns("X:X").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("W:W").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft


For Each mycell3 In Range("W2", Range("W" & myLastRow))
If mycell3.Value = "TRUE" Then
    mycell3.Offset(, 32).Value = "TRUE"
Else
    mycell3.Offset(, 32).Value = "FALSE"
End If
Next mycell3


Range("BD2").Formula = "=IF(AND(BA2*BB2*BC2=1),TRUE,FALSE)"
Range("BD2").Select
Selection.AutoFill Destination:=Range("BD2:BD" & myLastRow)


    Range("BE2").Select
    Range("BE2").Formula = "=TRIM(CLEAN(BD2))"
    Range("BE2").Select
    Selection.AutoFill Destination:=Range("BE2:BE" & myLastRow)
    Columns("BE:BE").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("BD:BD").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft


For Each mycell4 In Range("BD2", Range("BD" & myLastRow))


If mycell4.Value = "TRUE" Then
    mycell4.Offset(, -6).Value = "No Action"
    mycell4.Offset(, -5).Value = "No Error"
    mycell4.Offset(, -4).Value = "Compliant"
End If
Next mycell4


    Columns("BA:BD").Select
    Selection.Delete Shift:=xlToLeft
    Range("BA80").Select


End Sub

 

[Tetra201]

Many Forum users, including myself, are unable or unwilling to access file-sharing sites.

Please see if the following code fragment is of any help to you:

For Each mycell In Range("C2:C" & myLastRow)
    If (UCase(mycell.Value) Like "*NONHAZ*" Or _
        mycell.Value Like "*UN2911*" Or _
        mycell.Value Like "*UN3316*") And _
        Len(mycell.Offset(, 18).Value) = 8 And _
        mycell.Offset(, 20).Value = "TRUE" _
    Then
        mycell.Offset(, 47).Value = "No Action"
        mycell.Offset(, 48).Value = "No Error"
        mycell.Offset(, 49).Value = "Compliant"
    End If
Next mycell

 

[Sphinx404]

@Joe4 @Tetra201

There are 2 specific questions within the code I am unsure of highlighted in RED, if you could please help me out with those. Everything is TEXT, there are no boonleans.

How does the following look?

Sub Macro2()
[B][COLOR=#008000]'THIS SUB: looks at a set of parameters that must be met in order for AX:AZ to be populated[/COLOR][/B]


Dim myLastRow As Long
Dim myworksheet As Worksheet


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set myworksheet = Worksheets("HazShipper")
myLastRow = myworksheet.Cells(myworksheet.Rows.Count, "A").End(xlUp).row


row = 2


'"A2", Range("E" & myLastRow)) - [B][COLOR=#ff0000]HOW TO "[/COLOR][COLOR=#0000ff]DO UNTIL[/COLOR][COLOR=#ff0000]" LAST ROW[/COLOR][/B]?
[B][COLOR=#008000]'I never know how to start the IF statement, in the past I always use "FOR EACH 'var1' in RANGE...[/COLOR][/B]


[COLOR=#0000ff]Do Until[/COLOR] Range("A2" & row).Value = ""


If Range("N" & row).Value = "TRUE" Then
If Range("W" & row).Value = "TRUE" Then
If Range("Y" & row).Value = "TRUE" Then
If Range("AC" & row).Value = "TRUE" Then
If Range("AF" & row).Value = "TRUE" Then
If Range("AI" & row).Value = "TRUE" Then
If Range("AM" & row).Value = "TRUE" Or "Within Limit" Then
If Range("AN" & row).Value = "TRUE" Then
If Range("AO" & row).Value = "FALSE" Then Range("AP" & row).Value = """"
If Range("AO" & row).Value = "TRUE" Then Range("AP" & row).Value = [B][COLOR=#ff0000]'MUST HAVE VALUE[/COLOR][/B]
If Range("AQ" & row).Value = "MATCH" Then
If Range("AR" & row).Value = "TRUE" Then
If Range("AS" & row).Value = "TRUE" Then
If Range("AT" & row).Value = "TRUE" Then
    Range("AX" & row).Value = "No Action"
    Range("AY" & row).Value = "No Error"
    Range("AZ" & row).Value = "Compliant"


row = row + 1


Loop


End Sub

 

[Joe4]

First thing, NEVER use reserved words (names of existing functions, properties, methods, objects, etc) as the name of your variables or procedures. This causes ambiguity which can lead to errors or unexpected results. So do not use "row" as a variable.

Do find the last row in any column, you can do this (for column E in this example):

Dim lrow as Long
lrow = Cells(Rows.Count,"E").End(xlUp).Row

So, then to loop through all rows in column E, you can do this:

Dim r as Long
For r = 2 to lrow
    If Range("N" & r).Value = "TRUE" Then
Next r

Actually, instead of referring to the cell like:

Range("N" & r)

you could also use:

Cells(r,"N")

I prefer to use Cells, just because I think it looks nicer, and is actually more flexible. For the column reference (second argument), you can use the column letter or column number, i.e. Cells(r,14). This comes in real handy if you are looping through columns.

 

[Sphinx404]

Thanks for the pointers @Joe4

I've made the suggested changes. And this is how a programmer would code it, but I'm still in my coding infancy, I rely on the keywords to tell me what's going on. Baby steps....

Sub macro3()


'THIS SUB: looks at a set of parameters that must be met in order for AX:AZ to be populated


Dim lrow As Long
Dim wksht1 As Worksheet


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set wksht1 = Worksheets("HazShipper")
lrow = wksht1.Cells(wksht1.Rows.Count, "A").End(xlUp).row


r = 2


For r = 2 To lrow


If Cells(r, 14).Value = "TRUE" Then
If Cells(r, 23).Value = "TRUE" Then
If Cells(r, 25).Value = "TRUE" Then
If Cells(r, 29).Value = "TRUE" Then
If Cells(r, 32).Value = "TRUE" Then
If Cells(r, 35).Value = "TRUE" Then
If Cells(r, 39).Value = "TRUE" Or "Within Limit" Then
If Cells(r, 40).Value = "TRUE" Then
If Cells(r, 41).Value = "FALSE" Then Cells(r, 42).Value = """"
If Cells(r, 41).Value = "TRUE" Then Cells(r, 42). [B][COLOR=#ff0000]'MUST HAVE VALUE[/COLOR][/B]
If Cells(r, 43).Value = "MATCH" Then
If Cells(r, 44).Value = "TRUE" Then
If Cells(r, 45).Value = "TRUE" Then
If Cells(r, 46).Value = "TRUE" Then
    Cells(r, 50).Value = "No Action"
    Cells(r, 51).Value = "No Error"
    Cells(r, 52).Value = "Compliant"


r = r + 1


Loop


End Sub

I'm still having trouble incorporating LEN into my THEN statement. I cannot find anything online that helps in the context I'm trying to use LEN... I can write a simple IF statement for LEN, but I do not know how to write one in this context. My own logic tells me that it should be:

If Cells(r, 41).Value = "TRUE" [COLOR=#0000ff]Then [/COLOR]Cells(r, 42)[B][COLOR=#ff0000].LEN >=1[/COLOR][/B]

but we both know that doesn't work. Any final suggestions for this macro?

 

[Joe4]

What exactly are you trying to do in that particular step?