Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Problem with Instr coding

    I am trying to do an Instr function but want to use two different criteria in my If-Then statement. Below is a shortened version of my current code. For example on the second line, if both "S/P" and "#04" are found in the string, I want to code the item "04 V". The fourth line is the same way. Is Instr the wrong thing to use here? Several of my other lines are just searching for one substring and I also have other lines going off of the left/right/mid of the string. Any help would be great!

    Code:
    Sub AutoCoding() 
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
        Set Desc = Range("E6") 
        Do Until IsEmpty(Desc.Offset(0, -4)) 
            With Desc 
                If InStr(1, Desc, "02999999") Then 
                    Desc.Offset(0, 2) = "02 I" 
                ElseIf InStr(1, Desc, "S/P") And InStr(1, Desc, "#04") Then 
                    Desc.Offset(0, 2) = "04 V" 
                ElseIf InStr(1, UCase(Desc), UCase("Admin Charge")) Then 
                    Desc.Offset(0, 2) = "J" 
                ElseIf InStr(1, Desc, "STOP PAY") And InStr(1, Desc, "#1") Then 
                    Desc.Offset(0, 2) = "01 V" 
                ElseIf Left(Desc, 3) = "ICB" Then 
                    Desc.Offset(0, 2) = "J" 
                End If 
                Set Desc = Desc.Offset(1, 0) 
            End With 
        ActiveWindow.SmallScroll Down:=1 
        Loop 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    End Sub
    Last edited by RetiredGeek; 2014-11-19 at 17:01. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    KMF,

    You don't give us any data to work with so it's hard to tell what the code is doing. Specifically, is the string you are searching in E6 or A6 (e.g.) Desc.Offset(0,-4).

    A worksheet with some test data would be most useful. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    KMF,

    Here's a modification of your code moving from ElseIf constructs to a Case construct which is easier to read IMHO!
    You'll also notice that I'm testing the Instr functions to be greater than 0, Instr returns the starting position of the found string which is always greater than 0 (it returns 0 if the string is not found). I've also killed the scroll command as this accomplishes nothing with Application.ScreenUpdating set to False.

    Another thing I personally don't like the use of the default object property, specifically DESC for Desc.Value it tends to hide what you are really doing. Thus the change, you already had the With Desc clause but were not taking advantage of it.
    Code:
    Option Explicit
    
    Sub AutoCodingCase()
    
       Dim Desc As Range
       
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
    
        Set Desc = Range("E6")
        
        Do Until IsEmpty(Desc.Offset(0, -4).value)
        
          With Desc
          
            Select Case True
                Case InStr(1, .Value, "02999999") > 0
                    .Offset(0, 2) = "02 I"
                Case (InStr(1, UCase(.Value), "S/P") > 0) And _
                     (InStr(1, .Value, "#04") > 0)
                    .Offset(0, 2) = "04 V"
                Case (InStr(1, UCase(.Value), UCase("Admin Charge")) > 0)
                    .Offset(0, 2) = "J"
                Case (InStr(1, UCase(.Value), "STOP PAY") > 0) And _
                     (InStr(1, .Value, "#1") > 0)
                    .Offset(0, 2) = "01 V"
                Case Left(UCase(.Value), 3) = "ICB"
                    .Offset(0, 2) = "J"
            End Select
            
         End With  'Desc
         
            Set Desc = Desc.Offset(1, 0)
            
        Loop
        
       Application.ScreenUpdating = True
       Application.Calculation = xlCalculationAutomatic
       
    End Sub   'AutoCodeingCase()
    kmf.JPG
    BTW: I've tested this with data I made up but a test file is always useful. HTH
    Last edited by RetiredGeek; 2014-11-19 at 20:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is perfect! Thank you for all of your help! I'll be sure to post test data next time.

Posting Permissions

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