Searching for string in a two dimensional array vba
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)
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".
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
Not sure why you have to loop for the columns since you know there's always 3... So this seems easier.
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.
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
You could also do this with a worksheet formula. For example, if E1 contains your column1 value; and B1 your column2 value, try:
I see a tree like structure and think xml but to keep it simple use a Dictionary...
In the VBA Editor - using the Tools/References menu add a reference to 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
Try creating a third column where you concatenate the values from three previous columns i.e. in D1 you would have =A1&B1&C1. Next use that in you vlookup or match. If you do not specify exact match then in case having multiple entries for c 1 you would get the first or last one, depending on comparison type used.