+ Reply to Thread
Results 1 to 10 of 10

Select Case statements after Search String not working

Hybrid View

  1. 12-09-2013, 06:28 PM #1
    rdowney79
    rdowney79 is offline
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Select Case statements after Search String not working

    I am attempting to use a search string that will take priority over some select statements. The Search String statements run correctly, but the Select Statements get skipped over and dont populate. 

    For Each pCell In Range("C2:C" & Range("B" & Rows.Count).End(3)(1).Row)
    If InStr(1, pCell.Value, "TSC") <> 0 Or _
       InStr(1, pCell.Value, "MOBILE") <> 0 Or _
       InStr(1, pCell.Value, "410WED") <> 0 Then
        
    Select Case Len(pCell)           
          Case Is = 6
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],3)"
           Case Is = 7
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],4)"
           Case Is = 8
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],5)"
           Case Is = 9
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 10
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)"
           Case Is = 11
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 12
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 13
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 14
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 15
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 16
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 17
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 20
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 21
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],15)"
           Case Is = 32
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 42
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 43
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 44
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
        End Select
    End If
Next pCell

LR = Range("B1").End(xlDown).Offset(1, 0).Row
Range("D2:D" & LR).Value = Range("D2:D" & LR).Value
    Thanks,
    Ray
    Reply With QuoteRegister To Reply
  2. 12-09-2013, 06:39 PM #2
    tfurnivall
    tfurnivall is offline
    Forum Contributor
    Join Date
    11-15-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Office 365
    Posts
    314

    Re: Select Case statements after Search String not working

    Negative logic sucks, doesn't it.

    Your code says:

    For each cell if the cell doesn't contain something OR doesn't contain something else OR doesn't contain a different something else then
     Do something nifty
next cell
    Well, if a cell DOES contain one of those somethings, then it most certainly does NOT contain any of the other somethings, so the only thing that will cause your nifty piece of code to execute will be if the cells contain NONE of the suggested strings. I don't think that's what you wanted.

    if negative condition 1 AND negative condition 2 AND negative condition three then
   do the oppoite of what you want
else
   DO YOUR SPIFFY STUFF!
endif
    may be a better and easier to read way of expressing your intent!

    HTH

    Tony
    Reply With QuoteRegister To Reply
  3. 12-09-2013, 07:02 PM #3
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Select Case statements after Search String not working

    Assuming that the search lines work. Try to use one of the lines for the select case range

    For Each pCell In Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)

OR

For Each pCell In Range("C2", Range("C" & Rows.Count).End(xlUp))
    Reply With QuoteRegister To Reply
  4. 12-10-2013, 11:15 AM #4
    rdowney79
    rdowney79 is offline
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Select Case statements after Search String not working

    thanks, his code update appears to work but has highlighted the fact that my search lines do not work correctly. Any suggestions of having the search string look for cells that contain the identified values within the text and return the entire cell contents if a match occurs?


    SubTest()
For Each pCell In Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row) 'your range can be whatever
    If InStr(1, pCell.Value, "TSC") <> 0 And _
    InStr(1, pCell.Value, "MOBILE") <> 0 And _
    InStr(1, pCell.Value, "410WED") <> 0 Then
    Else: Select Case Len(pCell)
           Case Is = 6
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],3)"
           Case Is = 7
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],4)"
           Case Is = 8
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],5)"
           Case Is = 9
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 10
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)"
           Case Is = 11
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 12
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 13
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 14
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 15
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 16
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 17
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 20
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 21
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],15)"
           Case Is = 32
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 42
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 43
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 44
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
        End Select
    End If
Next pCell

LR = Range("B1").End(xlDown).Offset(1, 0).Row
End Sub
    Reply With QuoteRegister To Reply
  5. 12-10-2013, 01:06 PM #5
    tfurnivall
    tfurnivall is offline
    Forum Contributor
    Join Date
    11-15-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Office 365
    Posts
    314

    Re: Select Case statements after Search String not working 

    If InStr(1, pCell.Value, "TSC") > 0 Or_
InStr(1, pCell.Value, "MOBILE") > 0 Or_
InStr(1, pCell.Value, "410WED") > 0 Then
    If Anyone of those conditions is true, then you want to do the Select statement, right?

    Consider the case where Instr(1,pCell.Value,"TSC") is > 0.

    Both of the other two conditions will return FALSE, and as a result the entire condition (joined by AND operators) will also return FALSE.

    That's what I meant when I said that negative logic sucks!

    Instr will return 0 or above, so the whole <>0 is unnecessary - it sucks you into the negative logic thingy. Test for what you want (or at least for what you can get easily), and then use an ELSE clause if necessary.

    HTH

    Tony
    Reply With QuoteRegister To Reply
  6. 12-10-2013, 01:34 PM #6
    rdowney79
    rdowney79 is offline
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Select Case statements after Search String not working

    Thank you for you assistance, but this is not what I'm attempting to do. What I'm hoping to accomplish is to have the InStr statements handle exceptions to the Select statements. So in the case of Instr(1,pCell.Value,"TSC") is > 0, if the contents of the pCell does contain the string "TSC", such as '83TSC91780', then I need the code to return the entire contents of pCell. Otherwise if the contents of pCell did not contain the "TSC" string, I need the code to perform the Select Statements. In a different example, if the contents of pCell did not contain the string "TSC", such as ('97UNKN9170'), then I would need the Case Is = 10 Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)" Select statement to be performed. Hope this is clearer than mud.

    Thanks again!

    Ray
    Reply With QuoteRegister To Reply
  7. 12-10-2013, 02:00 PM #7
    tfurnivall
    tfurnivall is offline
    Forum Contributor
    Join Date
    11-15-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Office 365
    Posts
    314

    Re: Select Case statements after Search String not working

    Sort of like this?
    For Each pCell In Range("C2:C" & Range("B" & Rows.Count).End(3)(1).Row)
    If InStr(1, pCell.Value, "TSC") <> 0 Or _
       InStr(1, pCell.Value, "MOBILE") <> 0 Or _
       InStr(1, pCell.Value, "410WED") <> 0 Then

'   "return the entire contents of pCell" whatever that means, and however you do it;)

    ELSE                                       '   !!!!        
    Select Case Len(pCell)           
          Case Is = 6
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],3)"
           Case Is = 7
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],4)"
           Case Is = 8
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],5)"
           Case Is = 9
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 10
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)"
           Case Is = 11
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 12
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 13
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 14
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
           Case Is = 15
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
           Case Is = 16
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
           Case Is = 17
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 20
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
           Case Is = 21
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],15)"
           Case Is = 32
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 42
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 43
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
           Case Is = 44
               Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
        End Select
    End If
Next pCell

LR = Range("B1").End(xlDown).Offset(1, 0).Row
Range("D2:D" & LR).Value = Range("D2:D" & LR).Value
    I think the issue all along has been When to do something, much more than What to do!

    I hope that waters are less turbid, now!

    Tony
    Reply With QuoteRegister To Reply
  8. 12-10-2013, 02:14 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Select Case statements after Search String not working

    Maybe ...

    Sub Test()
    Dim cell        As Range
    Dim iLen        As Long
    Dim s           As String

    For Each cell In Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)
        s = cell.Value2
        With Cells(cell.Row, "D")
            If InStr(1, s, "TSC") > 0 Or _
               InStr(1, s, "MOBILE") > 0 Or _
               InStr(1, s, "410WED") > 0 Then
                .Value2 = s

            Else
                iLen = Len(s)
                Select Case iLen
                    Case 6 To 13
                        .Value2 = Left(s, iLen - 3)
                    Case 14 To 17
                        .Value2 = Left(s, iLen - 6)
                    Case 20
                        .Value2 = Left(s, 11)
                    Case 21
                        .Value2 = Left(s, 15)
                    Case 32, 42 To 44
                        .Value2 = Left(s, 6)
                    Case Else
                        Stop
                End Select
            End If
        End With
    Next cell
End Sub
    Last edited by shg; 12-10-2013 at 03:55 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  9. 12-10-2013, 02:58 PM #9
    rdowney79
    rdowney79 is offline
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Select Case statements after Search String not working

    shg, that appears to work great. Ill do some more testing here in a bit to verify, but thank you!
    Reply With QuoteRegister To Reply
  10. 12-10-2013, 03:04 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Select Case statements after Search String not working

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Password and case statements codes not working, but not erroring either
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 09:32 AM
  2. Multiple IF statements, or a select case?
    By rob2k77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2013, 03:31 PM
  3. help with if then select case statements
    By timermel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2011, 05:35 PM
  4. Can arrays be identified by i = X TO Y statements? (Avoid select Case)
    By macyarab in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2009, 08:21 AM
  5. Select Case Statements
    By ladeda063610 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2008, 11:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1