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.
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,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."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"Dim Found As Boolean Dim Failed As BooleanThat 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.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
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 FunctionSub 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
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....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.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
Last edited by SamT; 10-28-2015 at 09:11 AM.
or:
c00 = "A ; A" c01 = "A ; A+" MsgBox Split(c00, " ; ")(1) & " ; " & Split(c00, " ; ")(0) = c01
Works brilliantly, thanks a lot!
Based on the original approach:
MsgBox InStr(c01 & " ; " & c01, " ; " & c00 & " ; ") > 0