Partial cell(or string) match in excel macro

stupidgal picture stupidgal · May 13, 2016 · Viewed 18.4k times · Source

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.

Answer

John Coleman picture John Coleman · May 13, 2016

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