Best Way to Search a 2D Array

mchac

Well-known Member
I need to search a 'column' in a 2D array to see if I can find a match to user input value. I'd prefer not to loop

I've found this: http://www.mrexcel.com/forum/excel-questions/63296-visual-basic-applications-function-search-array.html and specifically Rick's solution.

And I've found this: http://www.jpsoftwaretech.com/finding-values-in-an-array-without-looping/.

Both seem to be suitable only for 1D arrays. Is that correct?

Also, what does Chr$(1) mean (from the first link)?

Thanks for your help.
 

Blade Hunter

Well-known Member
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
 

Blade Hunter

Well-known Member
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.
 

mikerickson

MrExcel MVP
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
 

Blade Hunter

Well-known Member
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 :)
 

Blade Hunter

Well-known Member
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:

mikerickson

MrExcel MVP
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:

Blade Hunter

Well-known Member
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top