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

Answers


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:

G2: =INDEX(ThirdColumn,SUMPRODUCT((FirstColumn=E1)*(SecondColumn=E2)*ROW(ThirdColumn)))


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.


Need Your Help

automatically disable “build automatically” in eclipse during ant run

eclipse plugins ant automation build-process

Is there a way to automatically disable the "build automatically" setting in eclipse while an ant build is running?

Admob Best practice for a Viewpager

android caching android-viewpager admob

I am using a viewpager with fragments, the number of fragments is usually around 4 to 5. ATM I have an adview inside each of the fragment. But that would mean calling up 4 to 5 adviews ( which are ...