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.
Printable View
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.
"Split" will turn the strings into an arrayThen you need to loop thru each Array Variable,(strCel One & Two) to compare all items with all other items,Code:Dim strCelOne As Variant
'Or ,shorter but same result
Dim strCelTwo
strCelOne = Split(Range("whatever"), " ; ")
"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."Code: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"Code:Dim Found As Boolean
Dim Failed As Boolean
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.Code: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
Code: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
Code: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
Thank you for your reply. I'm sure it works wonderful, but I found another, shorter solution.
Shorter than ?
And please, be polite and post that solution here....Code:Sub M_snb()
c00="BB ; AA+"
c01="AA+ ; BB"
msgbox instr(c01 & " ; " & c01,c00)>0
End Sub
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.
This is what I came up with.Code: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
or:
Code:c00 = "A ; A"
c01 = "A ; A+"
MsgBox Split(c00, " ; ")(1) & " ; " & Split(c00, " ; ")(0) = c01
Works brilliantly, thanks a lot!
Based on the original approach:
Code:MsgBox InStr(c01 & " ; " & c01, " ; " & c00 & " ; ") > 0