I am developing this macro in Excel vba that will loop through a column of client names that are sorted from a to z and check to see which ones are similar and assign them the same client ID in the column adjacent to it. I am using the Like operator to compare the names but for some reason it is not picking up on the similarities between the Names. For example, I want it to think that Aetna and Aetna Medicaid are the same thing because they both contain Aetna. I am not sure if this operator was meant to do this and if not, what would be the best way to go about this. I have pasted the code below. It is in the preliminary stages so for now it just outputs a MsgBox instead of assigning the client ID. Also, the range is defined by a function which just sets it as a one column range. Any help would be greatly appreciated!
Function SetInternalClientID()
Sheet9.Activate
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Set rng2 = FindHeader("CLIENT NAME", Sheet9.Name)
Count = 0
For i = 73 To rng2.Rows.Count
ClientCheck = rng2.Cells(i, 1).Value Like rng2.Cells(i - 1, 1).Value
If ClientCheck = True Then
MsgBox (rng2.Cells(i, 1) & " Like " & rng2.Cells(i - 1, 1).Value)
Else
MsgBox (rng2.Cells(i, 1) & " NOT LIKE " & rng2.Cells(i - 1, 1).Value)
End If
Next i
End Function
The Like operator is often confused. It doesn't simply check if two strings match. Instead, the value to the left of the Like operator is the string to test against the pattern, which is to the right of the operator.
String_To_Test Like Pattern
With that said, an expression such as:
"Aetna Medical" Like "Aetna" would return true, but "Aetna" Like "Aetna Medical" would return False.