Partial cell(or string) match in excel macro

Question

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.

How is that code even working? variable names can't start with numbers. — SierraOscar, May 13 '16 at 13:15
So.. How can I changed it to make it work? Please enlighten me. Thank you — stupidgal, May 13 '16 at 13:50

John Coleman · Accepted Answer · 2016-05-13T14:11:33.373

5

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

edited May 13 '16 at 14:11

answered May 13 '16 at 11:07

John Coleman

51,337
7
54
119

Thank you for providing your help. However, I received a "compile error: Invalid qualifier" for Name2 when I run the coding. – stupidgal May 13 '16 at 11:30
@stupidgal I only had your code snippet to go on. I was assuming that `Name2` is a Range variable that was being used to loop over a range, but apparently that isn't the case. How is `Name2` declared? Perhaps you could edit your code to include the surrounding `Do Loop` (or even more of the code, perhaps the whole sub if it isn't too long). – John Coleman May 13 '16 at 11:37
@JohnColeman Previously I `Dim Name1 as string` (same for Name2) I have changed my coding as what you have edited, but when I run it, there's no response to it. I tested `If Name1.Value Like "*" & Name2.Value & "*" Then MsgBox CashName` , there's also no response, so I believe that something went wrong for the condition – stupidgal May 13 '16 at 14:32
@stupidgal try `Trim(Name1.Value) Like "*" & Trim(Name2.Value) & "*"` -- stray white space can mess up comparisons. – John Coleman May 13 '16 at 14:47
@stupidgal `"IT-executive Sally Lim" Like "*" & "Sally Lim" & "*"` definitely evaluates to `True`. Maybe put `Debug.Print Name1.Value & " " & Name2Value` in the code and tell us what you see. – John Coleman May 13 '16 at 14:52
@JohnColeman my bad, I realised that my Name1 was in capitals which caused the unresponsiveness. – stupidgal May 13 '16 at 15:16
@stupidgal I somethimes use things like `UCase(Trim(s)) Like "*" & UCase(Trim(t)) & "*"` to make comparisons insensitive to both case and surrounding white space. – John Coleman May 13 '16 at 15:19
@JohnColeman Thank you for your help. But what if my Name1 as ","? For example " Aaron, Jr"? – stupidgal May 13 '16 at 15:24
@stupidgal Experiment. If you can't figure it out post another question. Avoid the temptation to raise related but distinct questions in the comments. – John Coleman May 13 '16 at 15:28

score 3 · Answer 2 · answered May 13 '16 at 11:01

3

Use the Like operator:

If Name1 Like "*" & Name2 Then

You can use Like for specific pattern matching, but it also allows the wild character *

answered May 13 '16 at 11:01

SierraOscar

17,507
6
40
68

The coding above did not work. When I run it, there's no response to the condition – stupidgal May 13 '16 at 13:13
Then `Name1` obviously doesn't end with whatever `Name2` is. These answers are based on the code in your question - we can't see the values that you're seeing. – SierraOscar May 13 '16 at 13:51
Maybe I will make it check again – stupidgal May 13 '16 at 13:53

Partial cell(or string) match in excel macro

2 Answers2

Linked