Consulting

Results 1 to 8 of 8

Thread: Searching for multiple texts within a cell (InStr)

  1. #1
    VBAX Regular
    Joined
    Jun 2018
    Posts
    42
    Location

    Searching for multiple texts within a cell (InStr)

    Hi All,

    What am I doing wrong here?

    The IF statement works but I cant get the elseif statement to work where I want to check for multiple words within the cell?

    Many thanks

    Option Compare Text
    Sub Test()
    
    Dim lastrow As Long
    lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastrow
    
    If InStr(Range("A" & i).Value, "Red") > 0 Then
                Range("C" & i).FormulaR1C1 = "Correct"
                
         ElseIf InStr(Range("A" & i).Value, "Blue") > 0 And InStr(Range("A" & i).Value, "Green") > 0 Then
                Range("C" & i).Value = "Correct"
    
    Else
    
    'xxxxxx
    
    End If
    
    Next
    
    End Sub
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,187
    In VBA:

    Sub M_snb()
      sn = cells(1).currentregion
    
      for j = 1 to ubound(sn)
        if instr(sn(j,1),"Red") then sn(j,3) = "Red"
        if instr(sn(j,1),"Blue") and if instr(sn(j,1),"Green") then sn(j,3) = "Blue_Green"                 
      Next
    
      cells(1).currentregion = sn
    End Sub

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,335
    Looking at your sheet you have stuff in column B, is that line meant to be:
    ElseIf InStr(Range("A" & i).Value, "Blue") > 0 And InStr(Range("B" & i).Value, "Green") > 0 Then
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    I think P45cal is correct. That would make snb's and if instr(sn(j,1),"Green") should be and if instr(sn(j,2),"Green"). Do not use Option Explicit with snb's code.

    Another example is:
    Option Explicit
    Option Compare Text 'For case insensitivity of color names in cells
    
    Sub Test()
        For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
            If CBool(InStr(Cells(i, "A"), "Red")) Then
                Cells(i, "C") = "Correct"
            ElseIf CBool(InStr(Cells(i, "A"), "Blue") * InStr(Cells(i, "B").Offset(, 1), "Green")) Then
                Cells(i, "C") = "Correct"
            Else
                Cells(i, "C") = "Incorrect"
            End If
        Next
    End Sub
    The compiler is supposed to auto convert all parameters after an If to Booleans, but I like to make sure. ie, The CBool() is supposed to be redundant. Zero = False, all other numbers = True. The Multiplier Operator(*) is the Math equivalent of Boolean "And". (+) is the equivalent of "Or".
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Jun 2018
    Posts
    42
    Location
    Hi Guys,

    Sorry late reply. I realised I made a critical error in my mockup and should have explained myself better to solve the confusion, sorry. What I should have had was this:

    2021-05-01-075848.jpg

    So im looking within each cell, for multiple values, if both of those value exist then do something...
    Attached Files Attached Files

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    I don't see why your original code doesn't work

    snb's original code should work perfectly

    And if you need help to modify his or my code, you need more help than a Forum can offer
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Jun 2018
    Posts
    42
    Location
    Thanks SamT, I thought my original code would have worked so not sure why.

    I actually got your code working with the modification to look at the same cell.

    Many thanks

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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