Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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....


    VB:
    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?

    VB:
    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:

    VB:
    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:

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

    So you could use something like:

    VB:
    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 1 users browsing this thread. (0 members and 1 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