Consulting

Results 1 to 8 of 8

Thread: Comparing Strings

  1. #1

    Comparing Strings

    How can I write a macro so that when I compare two cells that are like this
    BB ; AA+
    and
    AA+ ; BB

    I can say they are equal. The format of the cells are always the same. Some letters, 1 space, a semicolon, 1 space and some more letters.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    "Split" will turn the strings into an array
    Dim strCelOne As Variant
    'Or ,shorter but same result
    Dim strCelTwo
    
    strCelOne = Split(Range("whatever"), " ; ")
    Then you need to loop thru each Array Variable,(strCel One & Two) to compare all items with all other items,
    Dim i As Long, j As long
    For i = lBound(strCelOne) to Ubound(strCelOne)
       For j = i to Ubound(strCelTwo)
        If strCelOne(i) = strCelTwo(j) THen
            'Do something
       End If
     Next j
    Next i
    "Do Something" is the confusing part. First we have to "remember" if an item in strCelTwo matched any item in strCelOne, but if it didn't match any item, we immediately know that the two Ranges aren't "equal."
    Dim Found As Boolean
    Dim Failed As Boolean
    "Do Something"
    Found = False ' Need to reset this for each loop thru strCelOne
    'For j blah, blah
        If strCelOne(i) = strCelTwo(j) Then
            Found = True
            Exit For 'Found a match, don't need to check any more items in strCelTwo.
        End If 
        'If we are still in this loop, then no match was found
        Failed = True
        Exit Sub 'We're done. At least one item in strCelTwo did not match
     Next j
    Next i
    End Sub
    That is really all the code you need to determine if the two cells are NOT "equal." If they're are Not unequal then they are ? However it is Best Practice to put all this code into a FUnction, that can be used by the sub that actually loops thru the Rows and does to the cells, what ever you need doing.

    Function VBAX_SamT_CellsMatch(rngCelOne As Range, rngCelTwo As Range) As Boolean
    'Returns True if Cells have matching substrings in them
    'For Help See: http://www.vbaexpress.com/forum/showthread.php?54118
    
    Dim CelOne, CelTwo
    Dim i As Long, j As long
    
    'Check if Same number of Sub Strings
    If Ubound(srCelOne) <> Ubound(strCelTwo) Then
       VBAX_SamT_CellsMatch = False
       Exit Function
    End If  
    
    For i = lBound(strCelOne) to Ubound(strCelOne)
       For j = i to Ubound(strCelTwo)
        If  strCelTwo(j) = strCelOne(i) Then
            GoTo iNext 'Found a match, don't need to check any more items in strCelTwo
       Else
            'If we are here, then a no match sub-String was found
        VBAX_SamT_CellsMatch = False
         Exit Function
        End If
     Next j
    iNext:
    Next i 'Compare next item in strCelOne
    
        'If we are still here, then everything matched up
        VBAX_SamT_CellsMatch = True
    End Function
    Sub LoopingthruRows()
    Dim rw As Long
    For rw = 1 to Cells(RowsCount, "A").End(xlUp).Row
       If Not VBAX_SamT_CellsMatch(Cells(rw, "A"), Cells(rw, "B")) Then
       'Do Something
    End If
    Next rw
    End Sub
    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

  3. #3
    Thank you for your reply. I'm sure it works wonderful, but I found another, shorter solution.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Shorter than ?
    Sub M_snb()
      c00="BB ; AA+"
      c01="AA+ ; BB"
      msgbox instr(c01 & " ; " & c01,c00)>0
    End Sub
    And please, be polite and post that solution here....

  5. #5
    Thank you! That's quite clever and it worked for about 90% of the data. However, when it compared A+ ; A with A ; A it results to "True". But I'm sure that's just due to you not knowing what the entire database looks like.

                If Mid(z, 1, InStr(1, z, ";") - 2) = Mid(x, InStr(1, x, ";") + 2, InStr(1, z, ";") - 2) _ 
     And Mid(x, 1, InStr(1, x, ";") - 2) = Mid(z, InStr(1, z, ";") + 2, InStr(1, x, ";") - 2) _
                    Then
    This is what I came up with.
    Last edited by SamT; 10-28-2015 at 09:11 AM.

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    or:

        c00 = "A ; A"
        c01 = "A ; A+"
        MsgBox Split(c00, " ; ")(1) & " ; " & Split(c00, " ; ")(0) = c01

  7. #7
    Works brilliantly, thanks a lot!

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Based on the original approach:

        MsgBox InStr(c01 & " ; " & c01, " ; " & c00 & " ; ") > 0

Posting Permissions

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