vba 在二维数组vba中搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24087563/
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
Searching for string in a two dimensional array vba
提问by rajeev
I have a 2D array in the following format. (Unsure how to format this so it appears in a table format. The first and second columns are 1 character each and the 3rd columns is 2 characters)
我有以下格式的二维数组。（不确定如何格式化它，因此它以表格格式显示。第一列和第二列各为 1 个字符，第三列为 2 个字符）
a 1 aa
a 2 ab
b 1 ba
b 2 bb
c 1 ca
c 2 cb
d 1 da
d 2 db
e 1 ea
e 2 eb
f 1 fa
f 2 fb
I need to first search for "c" in the first column. If that is found, I need to search for "2" in the second and find the corresponding value in the 3rd. In this case, I finally need the value "cb".
我需要先在第一列中搜索“c”。如果找到，我需要在第二个中搜索“2”并在第三个中找到相应的值。在这种情况下，我最终需要值“cb”。
Here is what I have so far but it isn't working correctly since I don't see the desired results
这是我到目前为止所拥有的，但它无法正常工作，因为我没有看到想要的结果
Public Sub Readinto_array()
Dim TheArray As Variant
Dim i As Long, j As Long, k As Long
Dim found As Boolean
TheArray = Range("G20:I31").Value
found = False
For i = LBound(TheArray) To UBound(TheArray)
For j = LBound(TheArray, 2) To UBound(TheArray, 2)
MsgBox TheArray(i, j)
If TheArray(i, j) <> "c" Then
Exit For
Else
If StrComp(TheArray(i, j + 1), "2", vbTextCompare) = 0 Then
MsgBox "found"
found = True
Exit For
End If
End If
Next j
If found Then
Exit For
End If
Next i
End Sub
采纳答案by xQbert
Not sure why you have to loop for the columns since you know there's always 3... So this seems easier.
不知道为什么你必须循环列，因为你知道总是有 3 个......所以这看起来更容易。
Public Sub Readinto_array()
Dim TheArray As Variant
Dim i As Long
TheArray = Range("G20:I31").Value
For i = LBound(TheArray) To UBound(TheArray)
If TheArray(i, 1) = "c" And TheArray(i, 2) = "2" Then
MsgBox (TheArray(i, 3))
End If
Next i
End Sub
Or further simplified using innate excel objects.
或者使用固有的 excel 对象进一步简化。
Public Sub Readinto_array()
Dim MyRange As Range
Set MyRange = Range("G20:I31")
For Each cell In MyRange
If cell.Value = "c" And Cells(cell.Row, cell.Column + 1) = "2" Then
MsgBox (Cells(cell.Row, cell.Column + 2).Value)
End If
Next
End Sub
回答by Ron Rosenfeld
You could also do this with a worksheet formula. For example, if E1 contains your column1 value; and B1 your column2 value, try:
您也可以使用工作表公式执行此操作。例如，如果 E1 包含您的 column1 值；和 B1 你的 column2 值，尝试：
G2: =INDEX(ThirdColumn,SUMPRODUCT((FirstColumn=E1)*(SecondColumn=E2)*ROW(ThirdColumn)))
回答by Dan Savage
I see a tree like structure and think xml but to keep it simple use a Dictionary...
我看到一个树状结构并认为 xml 但为了保持简单使用字典...
In the VBA Editor - using the Tools/References menu add a reference to Microsoft Scripting Runtime.
在 VBA 编辑器中 - 使用工具/引用菜单添加对 Microsoft Scripting Runtime 的引用。
Write a function to create the dictionary:
编写一个函数来创建字典：
Public Function LookErUp() As Dictionary
Dim i As Integer
Dim d As Dictionary
Set d = New Dictionary
Dim col1() As Variant
col1 = Array("a", "b", "c", "d", "e", "f")
Dim col2 As Dictionary
For i = 0 To UBound(col1)
Set col2 = New Dictionary
col2.Add 1, col1(i) & "a"
col2.Add 2, col1(i) & "b"
d.Add col1(i), col2
Next
Set LookErUp = d
End Function
You can test using the dictionary the Test procedure:
您可以使用字典测试测试程序：
Public Sub Test()
Dim ld As Dictionary
Set ld = LookErUp
If ld.Exists("c") Then
If ld("c").Exists(2) Then
MsgBox "Found " & ld("c")(2)
End If
End If
End Sub
回答by MP?kalski
Try creating a third column where you concatenate the values from three previous columns i.e. in D1you would have =A1&B1&C1. Next use that in you vlookupor match. If you do not specify exact match then in case having multiple entries for c 1you would get the first or last one, depending on comparison type used.
尝试创建第三列，在其中连接前三列的值，即在D1 中您将拥有的值=A1&B1&C1。接下来在vlookup或match 中使用它。如果您没有指定精确匹配，那么如果c 1有多个条目，您将获得第一个或最后一个，具体取决于使用的比较类型。
