（VBA）Excel：如果两列中的内容匹配则采取行动

(VBA) Excel : action if content in two columns is matching

提问by Phalanx

A picture is worth a thousand words so to make it clear, here is a picture to illustrate the situation:

一张图值一千字，所以为了清楚起见，这里有一张图来说明情况：

So I have in a column C some contents (string and integer) from C2to C16. I have another column G some city names (string) from G21to G25.

所以我在 C 列中有一些内容（字符串和整数）从C2到C16。我有另一列 G 一些城市名称（字符串）从G21到G25。

Here is what I try to do : I want my method probaCity() to look for matches between these two columns. If there is a match, I want it to display some value (10 for example) in the corresponding row of the G column (in the array "Probablity of being the city"), if no match, then it's gonna be 0.

这是我尝试做的：我希望我的方法 probaCity() 查找这两列之间的匹配项。如果有匹配项，我希望它在 G 列的相应行（在“城市概率”数组中）显示一些值（例如 10），如果没有匹配项，则它将为 0。

So, here in the cell G13it should write 10 as there is a match for "Quezon" and 0 in all the other cells of the array.

因此，在单元格G13 中，它应该写入 10，因为在数组的所有其他单元格中存在与“Quezon”和 0 匹配的匹配项。

Here is my code :

这是我的代码：

Sub probaCity()
Dim count As Integer, myResult As Boolean

For count = 2 To lastRow
Range("C" & count).Activate
myResult = IsNumeric(Application.Match(ActiveCell.Value, Range("G21:G25"), 0))

If myResult = True Then
Range("G" & ActiveCell.Row).Value = Range("G" & ActiveCell.Row).Value + 0
Else
Range("G" & ActiveCell.Row).Value = Range("G" & ActiveCell.Row).Value + 10
End If

Next count
End Sub

The problem with this code is that myResult returned value is always false (so everything is set to 0 in my array).

这段代码的问题在于 myResult 返回的值始终为 false（因此我的数组中的所有内容都设置为 0）。

Thanks a lot for your help.

非常感谢你的帮助。

EDIT: after trying a suggestion:
1/ Using VBA (working for everything but "Quezon") 2/ Using formulas... same issue.

编辑：尝试建议后：
1/ 使用 VBA（适用于除“奎松”以外的所有内容） 2/ 使用公式...同样的问题。

回答by Siddharth Rout

I want my method probaCity() to look for matches between these two columns. If there is a match, I want it to display some value (10 for example) in the corresponding row of the G column (in the array "Probablity of being the city"), if no match, then it's gonna be 0.

我希望我的方法 probaCity() 查找这两列之间的匹配项。如果有匹配项，我希望它在 G 列的相应行（在“城市概率”数组中）显示一些值（例如 10），如果没有匹配项，则它将为 0。

No Need to use VBA for this.

无需为此使用 VBA。

See this example

看这个例子

Use this Array formula. You will have to press CTRL+SHIFT+ENTERafter entering the formula

使用这个数组公式。输入公式后，您必须按CTRL+ SHIFT+ENTER

=IF(COUNT(FIND($C:$C,A1)),"10","")

Screenshot

截屏

EDIT: Since you want a VBA method, try this.

编辑：既然你想要一个 VBA 方法，试试这个。

VBA METHOD (this will work with the sample data as per your original post)

VBA 方法（这将根据您的原始帖子使用示例数据）

Sub Sample()
    Dim ws As Worksheet
    Dim LookUpRange As Range
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> This is your city list
        Set LookUpRange = .Range("G21:G25")

        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            .Range("G" & i).Value = Application.Evaluate("=IF(COUNT(FIND(" & _
                                    LookUpRange.Address & _
                                    "," & .Range("C" & i).Address & ")),""10"",""0"")")
        Next i
    End With
End Sub

回答by Aeronth

The problem with this code is that myResult returned value is always false (so everything is set to 0 in my array).

这段代码的问题在于 myResult 返回的值始终为 false（因此我的数组中的所有内容都设置为 0）。

Did you make sure that myResult is always false, or have you deduced it from the 0 values displayed?

您是否确保 myResult 始终为假，还是从显示的 0 值中推断出它？

Not sure if it will help, but IsErrorfeels more natural than IsNumerichere. Your original If statement would be in the right order.

不确定它是否会有所帮助，但IsError感觉比IsNumeric这里更自然。您的原始 If 语句将按正确顺序排列。

回答by Robert Co

No need for VBA.

不需要VBA。

1. Type formula =IF(ISNA(MATCH(C2,$G$21:$G$25,0)),0,10)into G2.
2. Copy G2to G3:G17.

1. 将公式=IF(ISNA(MATCH(C2,$G$21:$G$25,0)),0,10)输入到G2.
2. 复制G2到G3:G17.