Best Way to Search a 2D Array

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could loop through 1 dimension of the array creating a single dimension array each time and searching that in one go.

If you just want to know if something exists ie true or false rather than the location of it you could use Join to join the array to a string then check the len vs the len of the same thing with what you are searching for replaced with nothing, if they differ then it exists.

I can't see how you can do it all in one go across dimensions though

Here is an example:

Code:
Sub ArrStuff()
Dim MyArr As Variant, X As Long, FindVal As String
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
    If Len(Join(MyArr(X), ",")) <> Len(Replace(Join(MyArr(X), ","), FindVal, "")) Then
        MsgBox "Found in Dimension: " & X + 1 '+1 because my option base is 0, take this out if your option base is 1
    End If
Next
End Sub
 
Upvote 0
Having said that though, if you know how many elements in the first dimension you could do something like this, this works without looping:

Code:
Sub ArrStuff()
Dim MyArr As Variant, FindVal As String, StringOfArr As String
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
StringOfArr = Join(MyArr(0), ",") & "," & Join(MyArr(1), ",") & "," & Join(MyArr(2), ",")
If Len(StringOfArr) <> Len(Replace(StringOfArr, FindVal, "")) Then
    MsgBox "Found in array"
End If
End Sub

Make a string out of the joined elements. To make it dynamic you would need to loop through dimension 1 if there is no guarantee of the number of elements though.
 
Upvote 0
The Chr(1) in Ricks code is a delimiter that is not going to be in any of the data strings. Using it as a delimiter makes the routine robust against a user whose array values include common delimiters like space, comma or semi-colon.

If you have a twoD array and are only searching one column, you can use the INDEX function to restrict MATCH to that one column.

Code:
Dim myArray(1 To 4, 1 To 3)
Dim rowFound As Variant
Dim searchTerm As String
Dim columnToSearch As Long

myArray(1, 1) = "alpha1": myArray(1, 2) = "alpha2": myArray(1, 3) = "alpha3"
myArray(2, 1) = "beta1": myArray(2, 2) = "beta2": myArray(2, 3) = "beta3"
myArray(3, 1) = "gamma1": myArray(3, 2) = "gamma2": myArray(3, 3) = "gamma3"
myArray(4, 1) = "delta1": myArray(4, 2) = "delta2": myArray(4, 3) = "delta3"

columnToSearch = 2
searchTerm = "gamma2"

rowFound = Application.Match(searchTerm, Application.Index(myArray, 0, columnToSearch), 0)

If IsError(rowFound) Then
    MsgBox searchTerm & " not found"
Else
    MsgBox searchTerm & " found in row " & rowFound & ", column " & columnToSearch
End If
 
Upvote 0
The Chr(1) in Ricks code is a delimiter that is not going to be in any of the data strings. Using it as a delimiter makes the routine robust against a user whose array values include common delimiters like space, comma or semi-colon.

Nice :). Saved that little tidbit off for future use :)
 
Upvote 0
Added in CHR(1) and used Mikes awesome index code.

Still looping dimension1

Code:
Sub ArrStuff()
Dim MyArr As Variant, FindVal As String, StringOfArr As String, X As Long
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
    If Not IsError(Application.Match(FindVal, Application.Index(MyArr, 0, X + 1), 0)) Then
        MsgBox "Found in:" & Application.Match(FindVal, Application.Index(MyArr, 0, X + 1), 0) - 1 & "," & X
        Exit For
    End If
Next
End Sub
 
Last edited:
Upvote 0
Mike what does the '$' accomplish?

In Chr$(1) the $ is optional notation that the function returns a string value.
It is a hold over from BASIC, where every string function ended in $

Also, if this array is a range of cells, the worksheet function

=MIN(IF(DATARANGE="searchTerm",ROW(DATARANGE)+COLUMN(DATARANGE)/1000)) will return the row.column of the first cell in DATARANGE that holds searchTerm.
 
Last edited:
Upvote 0
Interestingly enough Mike, your code doesn't like an array where dimension 2 is variable length. I don't however understand why. Hoping you can break it down for me?

In the meantime I have tightened up my original code to give the address and verified it does work with variable length dimensions

Code:
Sub ArrStuff2()
Dim MyArr As Variant, X As Long, FindVal As String
FindVal = "Eight"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six", "Hi"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
    If Len(Join(MyArr(X), Chr(1))) <> Len(Replace(Join(MyArr(X), Chr(1)), FindVal, "")) Then
        MsgBox "Found at location: " & X & "," & Len(Left(Join(MyArr(X), Chr(1)), InStr(1, Join(MyArr(X), Chr(1)), FindVal) - 1)) - Len(Replace(Left(Join(MyArr(X), Chr(1)), InStr(1, Join(MyArr(X), Chr(1)), FindVal) - 1), Chr(1), ""))
    End If
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top