Code:
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