Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Case statement with inStr not workin

  1. #1
    Join Date
    26th October 2004
    Posts
    4

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th September 2004
    Posts
    438
    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

  3. #3
    Join Date
    14th June 2013
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Case Statement With Vlookup
    By julian_t in forum EXCEL HELP
    Replies: 8
    Last Post: April 11th, 2007, 19:07
  2. Something Similar To A Case Statement
    By Rithin in forum Excel and/or Email Help
    Replies: 3
    Last Post: October 19th, 2006, 22:20
  3. Select case statement
    By Re devil in forum EXCEL HELP
    Replies: 3
    Last Post: January 17th, 2006, 19:30
  4. Select Case Statement
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 11th, 2005, 16:21
  5. [Solved] VBA: Case Statement
    By Raydreamk in forum EXCEL HELP
    Replies: 6
    Last Post: July 31st, 2003, 22:09

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
  •  
porno