locked
My Select Case with Nested Like Function isn't working RRS feed

  • Question

  • My Code:

    Sub SelectCaseTestUTH()

    Dim RowNum As Long
    RowNum = 2
    Do Until Cells(RowNum, 4).Value = ""
        Select Case Cells(RowNum, 4).Value
            Case Is = Cells(RowNum, 4).Value Like "2ERWF*"
                Cells(RowNum, 5).Value = "WAFER"
            Case Is = Cells(RowNum, 4).Value Like "SE*"
                Cells(RowNum, 5).Value = "PARTS"
            Case Is = Cells(RowNum, 4).Value Like "SM*"
                Cells(RowNum, 5).Value = "BUSINESS SUPPORT"
            Case Is = Cells(RowNum, 4).Value Like "CH-WT*"
                Cells(RowNum, 5).Value = "CHEMICAL"
            Case Else
                Cells(RowNum, 5).Value = "OTHER"
        End Select
    RowNum = RowNum + 1
    Loop

    End Sub

    My Result:

    Items that start with SE- and SM- are being defined as "Other"!! Is there something wrong with my 'Like' function that doesn't recognize that prefix?

    Thursday, January 18, 2018 1:53 PM

Answers

  • Try this:

        Select Case True
            Case Cells(RowNum, 4).Value Like "2ERWF*"
                Cells(RowNum, 5).Value = "WAFER"
            Case Cells(RowNum, 4).Value Like "SE*"
                Cells(RowNum, 5).Value = "PARTS"
            Case Cells(RowNum, 4).Value Like "SM*"
                Cells(RowNum, 5).Value = "BUSINESS SUPPORT"
            Case Cells(RowNum, 4).Value Like "CH-WT*"
                Cells(RowNum, 5).Value = "CHEMICAL"
            Case Else
                Cells(RowNum, 5).Value = "OTHER"
        End Select


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Chris0405 Thursday, January 18, 2018 3:03 PM
    Thursday, January 18, 2018 2:10 PM
  • Hmm, I received the following error: Run-time error '1004': Application-defined or object-defined error.

    There are approximately 4,500 rows that I'm running this for. 

    Not sure on which line of code the error occurs but you may need to fully qualify the reference to the Cells. Below is one way:

    Worksheets("WorksheetName").Cells(RowNum, 4).Value Like "2ERWF*"
    If this is not the issue then you will probably need to identify the line of code associated with the error.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Chris0405 Thursday, January 18, 2018 3:04 PM
    Thursday, January 18, 2018 2:54 PM

All replies

  • Try this:

        Select Case True
            Case Cells(RowNum, 4).Value Like "2ERWF*"
                Cells(RowNum, 5).Value = "WAFER"
            Case Cells(RowNum, 4).Value Like "SE*"
                Cells(RowNum, 5).Value = "PARTS"
            Case Cells(RowNum, 4).Value Like "SM*"
                Cells(RowNum, 5).Value = "BUSINESS SUPPORT"
            Case Cells(RowNum, 4).Value Like "CH-WT*"
                Cells(RowNum, 5).Value = "CHEMICAL"
            Case Else
                Cells(RowNum, 5).Value = "OTHER"
        End Select


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Chris0405 Thursday, January 18, 2018 3:03 PM
    Thursday, January 18, 2018 2:10 PM
  • Hmm, I received the following error: Run-time error '1004': Application-defined or object-defined error.

    There are approximately 4,500 rows that I'm running this for. 

    Thursday, January 18, 2018 2:21 PM
  • Hmm, I received the following error: Run-time error '1004': Application-defined or object-defined error.

    There are approximately 4,500 rows that I'm running this for. 

    Not sure on which line of code the error occurs but you may need to fully qualify the reference to the Cells. Below is one way:

    Worksheets("WorksheetName").Cells(RowNum, 4).Value Like "2ERWF*"
    If this is not the issue then you will probably need to identify the line of code associated with the error.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Chris0405 Thursday, January 18, 2018 3:04 PM
    Thursday, January 18, 2018 2:54 PM
  • Thanks Paul/Hans - 

    Hans' original reply was correct, but Paul - you pointed me in the right direction as I had missed a step. Thanks!

    Thursday, January 18, 2018 3:05 PM