excel宏中的部分单元格(或字符串)匹配 [英] Partial cell(or string) match in excel macro
问题描述
我是VBA的新人,我想在两张表之间进行部分字符串(或单元格)匹配。
Name1的一个例子是IT执行Sally Lim
Name2的一个例子是Sally Lim
Name1 = Sheets(Work)。单元格(RowName1,ColName1)
Name2 = Sheets(Roster)。单元格(RowName2,ColName2)
' work
如果Name1 =*&姓名2& *然后
'Name2出现编译错误:无效的限定符
名称2.Font.Strikethrough
退出执行
否则
结束如果
但是,它不工作。当我运行编码时,没有任何反应或出现错误。请帮助
编辑编码:
如果ShiftName像* &安培; CashName& *然后
CashName.Font.Strikethrough = True
删除部分已解决并且在John Coleman提出的将我的声明从string更改为range之后,它再也没有显示Compile Error。
我通过更改Name1进行测试和Name2给Sally,然后使用以下条件进行删除,它可以工作。我相信这是*,使条件不可行。
如果ShiftName像CashName那么
CashName Font.Strikethrough = True
如何通过相应地更改条件来完成部分匹配? p>
第二编辑:
我的屁!我意识到我的Name1在CAPTIALS中。
除了@MacroMan关于使用像
一样,您还需要正确使用删除线
。它是一个布尔属性,需要设置为True:
如果Name1 Like*& Name2然后
Name2.Font.Strikethrough = True
退出Do
Else
如果
编辑:
根据您扩展的问题,您可以这样做:
Dim Name1 As Range,Name2 As Range'如果您没有已经声明为
'。在循环中:
设置Name1 = Sheets(Work)。单元格(RowName1,ColName1)
设置Name2 = Sheets(Roster)。单元格(RowName2,ColName2)
如果Name1.Value像*& Name2.Value& *然后
Name2.Font.Strikethrough = True
退出Do
Else
如果
在Range变量上使用 .Value
并不是必需的(使用 code>将按照预期的方式工作,而不需要它),但是许多人认为使用范围变量时要明确地使用良好的VBA编码风格,而不是依赖于范围对象的默认属性。
您还可以完全免除变量 Name1
和 Name2
If Sheets(Work)。Cells(RowName1,ColName1).Value Like*&表格(名册)。单元格(RowName2,ColName2).Value& *然后
Sheets(Roster)。Cells(RowName2,ColName2).Font.Strikethrough = True
退出Do
Else
如果
最后一句话: Else
紧跟着 End If
有点无意义。大概你的实际代码在else子句中做了一些事情。如果没有 - 只需删除其他的,并且具有结束如果
紧跟在 Exit Do
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
这篇关于excel宏中的部分单元格(或字符串)匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!