Searching for string in a two dimensional array vba

Question

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

I think the j+1 simply needs to be j+2.

xQbert
– xQbert

2014-06-06 17:47:10 +00:00
Commented Jun 6, 2014 at 17:47 — xQbert
– xQbert, Commented Jun 6, 2014 at 17:47

xQbert · Accepted Answer · 2014-06-06 18:14:34Z

1

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

edited Jun 6, 2014 at 18:14

answered Jun 6, 2014 at 17:52

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rajeev Over a year ago

Thanks. It was the first time using arrays. Guess got a little bit confused looking at the examples.

xQbert Over a year ago

@rajeev Not a problem. Sometimes you have to step back from the weeds to see if there's an easier method. You didn't have to use the array.

David Zemens Over a year ago

If the data range is very large, it will be faster to use arrays than to iterate the worksheet object. Both will work, it's just a matter of performance.

rajeev Over a year ago

Thanks @xQbert. As mentioned by David the data range is a little bit larger. Using your first solution worked great !!

Ron Rosenfeld · Accepted Answer · 2014-06-06 19:54:26Z

1

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)))

enter image description here

answered Jun 6, 2014 at 19:54

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

1 Comment

rajeev Over a year ago

I knew the function sumproduct was there. Never found a practical way to use it until I saw your function

Dan Savage · Accepted Answer · 2014-06-07 05:47:09Z

1

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

answered Jun 7, 2014 at 5:47

Dan Savage

112 bronze badges

1 Comment

rajeev Over a year ago

That is new for me. Never knew of that one before. Thanks

MPękalski · Accepted Answer · 2014-06-06 17:45:35Z

0

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.

answered Jun 6, 2014 at 17:45

MPękalski

7,1045 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

Searching for string in a two dimensional array vba

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related