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屋！