I am new to VBA and I would like to do a partial string (or cell) match between two sheets.
An example of Name1 would be "IT executive Sally Lim"
An example of Name2 would be "Sally Lim"
Name1 = Sheets("Work").Cells(RowName1, ColName1)
Name2 = Sheets("Roster").Cells(RowName2, ColName2)
'This condition doesn't work
If Name1 = "*" & Name2 & "*" Then
'The "Name2" comes out with a compile error: Invalid Qualifier
Name2.Font.Strikethrough
Exit Do
Else
End If
However, it is not working. When I run the coding, either nothing happens or an error pops out. Please help
Edited coding:
If ShiftName Like "*" & CashName & "*" Then
CashName.Font.Strikethrough = True
The strikeout portion has been solved and it does not shows the "Compile Error" anymore after I change my declaration from "string" to "range" as proposed by John Coleman.
I tested by changing Name1 and Name2 to both Sally and then use the following condition for strikeout and it works. I believed that it is the "*" that has made the condition unworkable.
If ShiftName Like CashName Then
CashName.Font.Strikethrough = True
How can a partial match be done by changing the condition accordingly?
SECOND EDIT:
MY BAD! I realised that my Name1 was in CAPTIALS.
In addition to @MacroMan 's answer about using Like
, you would also need to use Strikethrough
correctly. It is a Boolean property which needs to be set to True:
If Name1 Like "*" & Name2 Then
Name2.Font.Strikethrough = True
Exit Do
Else
End If
On Edit:
Based on your expanded question, you could do something like this:
Dim Name1 As Range, Name2 As Range 'If you don't have this already declared
'then ... in the loop:
Set Name1 = Sheets("Work").Cells(RowName1, ColName1)
Set Name2 = Sheets("Roster").Cells(RowName2, ColName2)
If Name1.Value Like "*" & Name2.Value & "*" Then
Name2.Font.Strikethrough = True
Exit Do
Else
End If
It isn't strictly necessary to use .Value
on Range variables (the comparison using Like
would work as expected without it) but it is considered by many to be good VBA coding style to be explicit when using range variables as opposed to relying on the default property of range objects.
You could also dispense with the variables Name1
and Name2
entirely:
If Sheets("Work").Cells(RowName1, ColName1).Value Like "*" & Sheets("Roster").Cells(RowName2, ColName2).Value & "*" Then
Sheets("Roster").Cells(RowName2, ColName2).Font.Strikethrough = True
Exit Do
Else
End If
A final remark: The Else
followed immediately by End If
is somewhat pointless. Presumably your actual code does something in the else clause. If not -- just delete else entirely and have End If
immediately after the Exit Do