vba 使用excel vba查找匹配值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18795974/
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
Finding matching values using excel vba
提问by user2759430
Without using excel built-in filter or pivot table function, I want to extract some results using vba. Consider the following example: given data in coloumn A and B, I want to be able to input "a" in C1 and using the vba to get in Column D all the corresponding values from column B (1,3,5). If I input "b", I get 2, 6, so on. Thanks. 
不使用excel内置过滤器或数据透视表功能，我想使用vba提取一些结果。考虑以下示例：给定 A 列和 B 列中的数据，我希望能够在 C1 中输入“a”并使用 vba 在列 D 中获取列 B (1,3,5) 中的所有相应值。如果我输入“b”，我会得到 2、6，依此类推。谢谢。
回答by Josh Kendrick
Sub GenerateMatches()
With ActiveSheet
FinalRowA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
InputLetter = ActiveSheet.Cells(1, 3)
NextRow = 1
For i = 1 To FinalRowA
If .Cells(i, 1) = InputLetter Then
.Cells(NextRow, 4) = .Cells(i, 2)
NextRow = NextRow + 1
End If
Next i
End With
End Sub
Obviously, you can make this alot more dynamic and faster, but this should get the job done.
显然，您可以使这更加动态和更快，但这应该可以完成工作。
回答by Siddharth Rout
I understand when you say you didn't want to use filters or Pivots. But Formulas?
我理解你说你不想使用过滤器或数据透视表。但是公式？
If something is possible with formulas then why VBA?
如果公式可以实现，那么为什么要使用 VBA？
Paste this Array Formula in Cell D1and press CTRL+ SHIFT+ ENTERand pull it down.
粘贴此数组公式Cell D1并按CTRL+ SHIFT+ENTER并将其下拉。
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$1,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$1,ROW($A$1:$A$7)),ROW(1:1)),2))
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$1,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$1,ROW($A$1:$A$7)),ROW(1:1)),2))
Screenshot
截屏
回答by Gary's Student
Enter the following event macro in the worksheet code area:
在工作表代码区域输入以下事件宏：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim D1 As Range, K As Long
Dim N As Long, NN As Long
Set D1 = Range(Range("D1"), Range("D1").End(xlDown))
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
v = Target.Value
K = 1
D1.Clear
NN = Cells(Rows.Count, "A").End(xlUp).Row
For N = 1 To NN
If v = Cells(N, "A").Value Then
Cells(K, "D").Value = Cells(N, "B").Value
K = K + 1
End If
Next
Application.EnableEvents = True
End Sub
Because it is worksheet code, it is very easy to install and automatic to use:
因为是工作表代码，所以很容易安装，自动使用：
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
- 右键单击 Excel 窗口底部附近的选项卡名称
- 选择查看代码 - 这会打开一个 VBE 窗口
- 粘贴内容并关闭 VBE 窗口
If you have any concerns, first try it on a trial worksheet.
如果您有任何疑虑，请先在试用工作表上尝试。
If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
如果您保存工作簿，宏将与它一起保存。如果您使用的是 2003 年以后的 Excel 版本，则必须将文件另存为 .xlsm 而不是 .xlsx
To remove the macro:
要删除宏：
- bring up the VBE windows as above
- clear the code out
- close the VBE window
- 调出如上的 VBE 窗口
- 清除代码
- 关闭 VBE 窗口
To learn more about macros in general, see:
要了解有关一般宏的更多信息，请参阅：
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
和
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
要了解有关事件宏（工作表代码）的更多信息，请参阅：
http://www.mvps.org/dmcritchie/excel/event.htm
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
必须启用宏才能使其工作！
B.T.W.
顺便提一句
You can do the same thing with formulas and no VBA - like VLOOKUP on steriods!
你可以用公式做同样的事情而不用 VBA - 就像在 steriods 上的 VLOOKUP！
