Announcement

Collapse
No announcement yet.

Case statement with inStr not workin

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Case statement with inStr not workin

    Hi all. Im tryin to clean up a bit of code here..I've got the following if/then/else statement that does what I want it do....


    Code:
    For Each rngTaxes In Range("A1:" & y)
    rngTaxes.Activate
    CurRw = ActiveCell.Row
        If InStr(1, rngTaxes.Text, "(") Or InStr(1, rngTaxes.Text, ")") Then
            rngTaxes.Offset(0, 4).Formula = "=SUM(substitute(C" & CurRw & ","" "","""")*" & TotalTaxes & ")" 'Revised taxes
        End If
    Next
    If I change it to the following Case statement, neither of my conditions are met....How can i fix this?

    Code:
    For Each rngTaxes In Range("A1:" & y)
    rngTaxes.Activate
    CurRw = ActiveCell.Row
        'Calculates revised taxes
        
        Select Case rngTaxes.Text
            Case InStr(1, rngTaxes.Text, Trim("Airtime"))
            'Don't do anything
            MsgBox "Case 1"
            Case InStr(1, rngTaxes.Text, "(") Or InStr(1, rngTaxes.Text, ")")
            MsgBox "Case 2"
                rngTaxes.Offset(0, 4).Formula = "=SUM(substitute(C" & CurRw & ","" "","""")*" & TotalTaxes & ")" 'Revised taxes
        End Select
         
    Next
    Thanks in advance!

  • #2
    Select Case does not work that way. It is like a lot of If Thens, for example:

    Code:
    Select Case Test.Text
       Case "A"
          MsgBox "1"
       Case "B"
          Msgbox "2"
    End Select
    
    'Is the same as
    If Test.Text = "A" Then
       MsgBox "1"
    Else
       If Test.Text = "B" Then
          MsgBox "2"
       End If
    End If
    So in your code, your Select Case is like:

    Code:
    If rngTaxes.Text = InStr(1, rngTaxes.Text, Trim("Airtime")) Then
    which will never evaluate to True

    So you could use something like:

    Code:
    Option Explicit
    
    Public Function CaseExample(strTest As String) As Long
    
    Select Case True
        Case InStr(1, strTest, "Airline") > 0
            CaseExample = 1
        Case InStr(1, strTest, "(") > 0 Or InStr(1, strTest, ")") > 0
            CaseExample = 2
        Case Else
            CaseExample = 99
    End Select
    
    End Function
    
    Sub testit()
    
    MsgBox CaseExample("This is an Airline")
    
    MsgBox CaseExample("This is not an airline")
    
    MsgBox CaseExample("This is a (two)")
    
    End Sub
    Hope that helps to make Select Case clearer

    TJ
    Oh dear I need a beer
    Online Motorsport Game

    Comment


    • #3
      Re: Case statement with inStr not workin

      TJ,
      I realize this is an old thread, but if you're still lurking around, Thanks
      I'm and old COBOL programmer who likes to fiddle with utilities for my stuff.
      I was trying to take an if/elseif structure in an excel vba-macro and convert it to case for viewing pleasure.
      I'm taking the "Directions" from MS-Streets and Trips, and boiling it down to an Itinerary I can study.
      Your example is exactly what I needed to solve the problem.

      What I ended up with:
      ActiveSheet.Select
      Dim TestVal As String
      LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
      For R = LastRow To 2 Step -1
      TestVal = ActiveSheet.Cells(R, 4).Value
      Select Case True
      Case InStr(TestVal, "Turn ") > 0 Or _
      InStr(TestVal, "Keep ") > 0 Or _
      InStr(TestVal, "Bear ") > 0 Or _
      InStr(TestVal, "Take ") > 0 Or _
      InStr(TestVal, "At exit") > 0 Or _
      InStr(TestVal, "End of day") > 0 Or _
      InStr(TestVal, "Road name") > 0 Or _
      InStr(TestVal, "Entering ") > 0 Or _
      InStr(TestVal, "ture time") > 0 Or _
      InStr(TestVal, "Merge ") > 0 Or _
      InStr(TestVal, "Stay on") > 0 Or _
      InStr(TestVal, "Construction ") > 0 Or _
      InStr(TestVal, "Depart Refuel") > 0
      Rows(R).Delete
      Case InStr(TestVal, "Depart ") > 0
      If InStr(ActiveSheet.Cells(R, 4).Value, " [") Then
      ActiveSheet.Cells(R, 4).Select
      n = InStr(1, Cells(R, 4), "[")
      Cells(R, 4) = Left(Cells(R, 4), n - 1)
      End If
      ' On Arrive Refuel lines remove Arrive
      Case InStr(TestVal, "Arrive Refuel") > 0
      ActiveSheet.Cells(R, 4).Select
      n = Len(Cells(R, 4))
      Cells(R, 4) = Right(Cells(R, 4), n - 7)
      End Select
      Next R

      Comment

      Working...
      X