在 Excel VBA 中运行嵌套的 If/Match
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19190477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running a nested If/Match in Excel VBA
提问by Abhiously
Hey I've spent most of the day trying to figure this out - and I think I'm pretty close. The basic premise is that I have 4 columns of data. They are two separate columns of First Name/Last Name columns copied and pasted together. Now what I want to do is run a match on the last names and IF they are equal, run a match on the first names. The column ranges are dynamic which is why running a CONCATENATE and VLOOKUP formula works, but if I can get something less involved it'd be great.
嘿，我花了一天的大部分时间试图弄清楚这一点 - 我想我已经很接近了。基本前提是我有4列数据。它们是复制并粘贴在一起的名字/姓氏列的两个单独列。现在我想做的是对姓氏进行匹配，如果它们相等，则对名字进行匹配。列范围是动态的，这就是运行 CONCATENATE 和 VLOOKUP 公式有效的原因，但如果我能减少一些涉及的东西，那就太好了。
Here's a sample data table
A B C D E
1 Last First Last2 First2
2 Sharma Abhi Smith Kevin
3 Philip Matt Smith GEORGIA
4 Franc Pete John Bon Jovi
5 Arnold Susan Hyman White
6 Mallo Chad Sharma Katie
7 Daigle Steve Sharma Abhi
And my thought is that starting in cell E2 it should return a match or not a match (in this case only Row 2 should return a match. Currently it's returning a match everytime - which is def not right. I feel like I'm missing something small? Thanks for the help.
我的想法是，从单元格 E2 开始，它应该返回匹配项或不匹配项（在这种情况下，只有第 2 行应该返回匹配项。目前它每次都返回匹配项 - 这显然不对。我觉得我错过了小东西？谢谢你的帮助。
This is the code I've written so far
这是我到目前为止编写的代码
Sub matchFunction()
On Error Resume Next
Dim BW_Row As Long
Dim BW_Clm As Long
Table1 = Sheet2.Range("F11:F16") ' Range of all appointments last name
Table2 = Sheet2.Range("$G:$G") ' Range of all appointments first name
Table3 = Sheet2.Range("$H:$H") ' Range of leads
BW_Row = Sheet2.Range("J11").Row ' Change E column if it's a match
BW_Clm = Sheet2.Range("J11").Column
For Each c In Table1
For Each d In Table2
If Application.Match(c, Table3, 0) <> "" Then
If Application.Match(d, Table3, 0) <> "" Then
Sheet2.Cells(BW_Row, BW_Clm) = "It's a Match!"
Else
Sheet2.Cells(BW_Row, BW_Clm) = "Sorry, it's not a match"
End If
End If
BW_Row = BW_Row + 1
Next d
Next c
MsgBox "Matching Finished"
End Sub
回答by ARich
+1 for @user2140261's comment... the VBA is going to be slower than your formula. However, if you're set on using VBA, insert this instead of your For Each Cloop:
+1 @ user2140261 的评论...... VBA 会比你的公式慢。但是，如果您设置为使用 VBA，请插入它而不是您的For Each C循环：
i = 11
For Each c In Table1
If c = Cells(i, 8) Then
If Cells(i, 7) = Cells(i, 9) Then sheet2.Cells(i, BW_Clm) = "It's a Match!"
End If
sheet2.Cells(i, BW_Clm) = "Sorry, it's not a match"
i = i + 1
Next c
Tested
已测试
This will check F11against H11. If it's a match, it checks G11against I11, if that returns a match, "It's a match!"is written to J11. If it isn't a match, "Sorry, it's not a match"is written to J11. It then begins the loop over for row 12.
这将检查F11对H11。如果它是一个比赛，它会检查G11对I11，如果返回匹配，"It's a match!"被写入J11。如果不匹配，"Sorry, it's not a match"则写入J11. 然后它开始循环第 12 行。
回答by ARich
Using VBA seems unnecessary for this. You could just throw in this array formula in E2 (hit Ctrl+Shift+Enter):
为此，似乎没有必要使用 VBA。你可以在 E2 中输入这个数组公式（按 Ctrl+Shift+Enter）：
=CHOOSE(MAX(IF(($C$2:$C$7=$A2)*($D$2:$D$7=$B2),2,1)),"Sorry, it's not a match","It's a Match!")
=CHOOSE(MAX(IF(($C$2:$C$7=$A2)*($D$2:$D$7=$B2),2,1)),"Sorry, it's not a match","It's a Match!")
The IF function assigns value 2 if both conditions are TRUE and value 1 if FALSE. MAX will find the highest value out of the array of values. CHOOSE will return the phrase based on the value. 1 = "no match", 2 = "match".
如果两个条件都为 TRUE，则 IF 函数分配值 2，如果为 FALSE，则分配值 1。MAX 将从值数组中找到最大值。CHOOSE 将根据值返回短语。1 =“不匹配”，2 =“匹配”。
