Case macro help

[Tonyk1051]

Hi,

Can some one help create a Macro for the rules below for the worksheet in the link? would be much appreciated, thanks (i tried doing one myself using a "case" macro it was an utter failure...

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has open box in column W (Defect Cat)
then put in column H REEVALUATE - TO STOCK OR USED

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has Defective / unspecified in column W (Defect Cat)
then put in column H, MFR

if column W has keyword damaged and column O is less than $300 then put in column H, TL UNLESS if column J is tested confirmed defective or tested confirmed damaged put in column H, liq.com
if column T is 3years old from todays date then put in column H, TL
if column F is Viewsonic, LG or NEC and column W is Defective/ unspecified then in column H put pic of defect required
if column N has keyword television or monitors and column W is damaged then put in column H TL
if column N has keyword unlocked cell phones and column X is not all upper case text then put in column H, TT

test.xlsm heres the link to the file

 

[Tonyk1051]

Try the following:

Sub newTest()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
    Dim FirstRowOfData  As Long
    Dim LastRowInSheet  As Long
    Dim cel             As Range, Rng As Range
    Dim ThisCell        As String
    Dim wsDestination   As Worksheet
'
    FirstRowOfData = 2                                                                                                  ' <--- Set this value
    Set wsDestination = Sheets("Sheet17")                                                                                   ' <--- Set this to correct sheet
'
    LastRowInSheet = wsDestination.Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row    ' Find last used Row in sheet
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("F" & FirstRowOfData & ":F" & LastRowInSheet)                                                       ' F column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "ViewSonic", "LG", "NEC"
                If cel.Offset(0, 17) = "Defective / unspecified" Then cel.Offset(0, 2) = "PICTURE OF DEFECT REQUIRED"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("G" & FirstRowOfData & ":G" & LastRowInSheet)                                                       ' G column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "DENIED BY MANUFACTURE", "DENIED DEFECT", "DENIED PRODUCT TYPE OR SKU", "DENIED DIST. TIMEFRAME"
                If cel.Offset(0, 16) = "Open Box" Then cel.Offset(0, 1) = "REEVALUATE - TO STOCK OR USED"
                If cel.Offset(0, 16) = "Defective / unspecified" Then cel.Offset(0, 1) = "MFR"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("N" & FirstRowOfData & ":N" & LastRowInSheet)                                                       ' N column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "MOBILE-Unlocked Cell Phones"
                If cel.Offset(0, 10) <> UCase(cel.Offset(0, 10)) Then cel.Offset(0, -6) = "TT"                          ' If column X not all caps then ...
            Case "TELEVISIONS-Televisions", "COMPUTERS-Computer Monitors", "COMPUTER-Computer Monitor Adapters", "MULTIMEDIA-Commercial Monitors", "COMPUTER MONITORS-Calibration", "COMPUTERS-Portable Monitors"
                If cel.Offset(0, 9) = "Damaged" Then cel.Offset(0, -6) = "TL"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("T" & FirstRowOfData & ":T" & LastRowInSheet)                                                       ' T column
'
    For Each cel In Rng
        If DateAdd("yyyy", 3, cel) < Date Then cel.Offset(0, -12) = "TL"
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("W" & FirstRowOfData & ":W" & LastRowInSheet)                                                       ' W column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "Damaged"
                If cel.Offset(0, -8) < 300 Then
                    If cel.Offset(0, -13) = "tested confirmed defective" Or cel.Offset(0, -13) = "tested confirmed damaged" Then
                        cel.Offset(0, -15) = "liq.com"
                    Else
                        cel.Offset(0, -15) = "TL"
                    End If
                End If
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

Hi everything works except the damaged macro, i tweaked it since i only needed tested confirmed damaged and changed to -16 but it still doesnt work, it just puts
all damaged under 300 to TL in coulmn H regarless (rule should be if its damaged and under 300 put in column H TL BUT IF its damaged under 300 and has in column G Tested confirmed Damaged, put liq.com in column H instead. Are the changes i made not right?

 Set Rng = Range("W" & FirstRowOfData & ":W" & LastRowInSheet)                                                       ' W column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "Damaged"
                If cel.Offset(0, -8) < 300 Then
                    If cel.Offset(0, -16) = "tested confirmed damaged" Then
                        cel.Offset(0, -15) = "liq.com"
                    Else
                        cel.Offset(0, -15) = "TL"
                    End If
                End If
        End Select
    Next

 

[johnnyL]

Your first post says 'UNLESS if column J is tested confirmed defective or tested confirmed damaged'

Your last post says to check column G. Not J. Which column should be checked there?

 

[Tonyk1051]

That’s my mistake disregard the first post, it’s check column G

 

[johnnyL]

Your code changes look correct to me.

One thing you may want to check:

                    If cel.Offset(0, -16) = "tested confirmed damaged" Then

Check your spelling as well as lower case/upper case in that line.

Ex. is it "tested confirmed damaged" or "Tested confirmed damaged" ... make sure it matches what you have in the cell,

 

[Tonyk1051]

Your code changes look correct to me.

One thing you may want to check:

                    If cel.Offset(0, -16) = "tested confirmed damaged" Then

Check your spelling as well as lower case/upper case in that line.

Ex. is it "tested confirmed damaged" or "Tested confirmed damaged" ... make sure it matches what you have in the cell,

You’re right it worked, thanks so much. One last thing about macros, I have two separate codes that work when it’s by themselves but when I put them both onto the same “macro page” it doesn’t work, is there a way to combine them both?

Case "Samsung"

If InStr(1, cel.Offset(, 8), "smart", 1) <> 0 Then

cel.Offset(, 2) = "TL"

End If

Case "Samsung"

If InStr(1, cel.Offset(, 8), "TELEVISIONS-Televisions", 1) <> 0 Then

If cel.Offset(, 17) = "Defective / unspecified" Or cel.Offset(, 17) = "Damaged" Then

cel.Offset(, 2) = "MFR"

ElseIf cel.Offset(, 17) = "Open Box" Then

cel.Offset(, 2) = "reeval to stock or used"

End If

End If

 

[johnnyL]

    Case "Samsung"
        If InStr(1, cel.Offset(, 8), "smart", 1) <> 0 Then cel.Offset(, 2) = "TL"
        If InStr(1, cel.Offset(, 8), "TELEVISIONS-Televisions", 1) <> 0 Then
            If cel.Offset(, 17) = "Defective / unspecified" Or cel.Offset(, 17) = "Damaged" Then
                cel.Offset(, 2) = "MFR"
            ElseIf cel.Offset(, 17) = "Open Box" Then
                cel.Offset(, 2) = "reeval to stock or used"
            End If
        End If

 

[Tonyk1051]

    Case "Samsung"
        If InStr(1, cel.Offset(, 8), "smart", 1) <> 0 Then cel.Offset(, 2) = "TL"
        If InStr(1, cel.Offset(, 8), "TELEVISIONS-Televisions", 1) <> 0 Then
            If cel.Offset(, 17) = "Defective / unspecified" Or cel.Offset(, 17) = "Damaged" Then
                cel.Offset(, 2) = "MFR"
            ElseIf cel.Offset(, 17) = "Open Box" Then
                cel.Offset(, 2) = "reeval to stock or used"
            End If
        End If

JohnnyL thank you so much for all the help, appreciate it

 

[johnnyL]

Glad to help.