VBA Find a value in a "column" of a 2D array

Question

I was previously dynamically building a 1D array by looping down a column in a spreadsheet. Because I only wanted unique values in this array, I'm calling

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean.
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

on each element before adding it. So far, so good.

Now I need to have elements from 2 columns stored, so I need to expand this to a 2D array. I only need the original data element to be unique, but my original function will not accept a 2D array.

Is there a way to search just one "column" of a 2D array to see if a string exists?

I've thought of a few workarounds, including concatenating both values and keeping them in a 1D array, then parsing them out, etc, but I'd like to know if the "find if one element is unique" approach is possible.

Array(0,0) would be A1, Array(0,1) would be B1. when you loop just make sure to ref the 2nd part of the array as a constant e.g. column ref, and increment the first part e.g. the row — 99moorem, Jun 09 '15 at 15:05
Are the values being stored from your second column basically the corresponding values to the values you stored from your first column. For example will your values basically be: `A1:B1`, `A4:B4`, `A9:B9` ? Also can you post your looping function? — Newd, Jun 09 '15 at 15:06
Your best bet is to scrap using arrays and to use the `dictionary` data type instead. It will be much faster for retrieving the data from it. Here's how to get started http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure — Dean MacGregor, Jun 09 '15 at 15:07
You could use the Dictionary Object. The Key would determine the unique element and the two columns could be stored in the Item separated by a delimiter. — bilbo_strikes_back, Jun 09 '15 at 15:07

score 2 · Answer 1 · answered Jun 09 '15 at 15:48

This simple code will help you find string in 1D or 2D arrays. Check out how is numbered column index in your code, maybe If i = ColumnToCheck Then need little improve

Function IsInArray(stringToBeFound As String, arr As Variant, Optional ColumnToCheck As Integer = 1) As Boolean

For i = LBound(arr, 2) To UBound(arr, 2) 'columns
    If i = ColumnToCheck Then
        For j = LBound(arr, 1) To UBound(arr, 1) 'rows
            If arr(j, ColumnToCheck) = stringToBeFound Then
            IsInArray = True
            Exit Function
            End If
        Next j
    End If
Next i

End Function

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

Thanks to Dean MacGregor and Jason_Walker for the comments. I hadn't used dictionaries before, and they were exactly what was called for here. The following two questions got me where I needed to go.

If either of you wants to post a separate answer here, I'm happy to delete this and accept yours.

Does VBA have Dictionary Structure?

Iterate over VBA Dictionaries?

edited May 23 '17 at 11:54

Community

1
1

answered Jun 09 '15 at 16:41

nwhaught

1,562
1
15
36

99moorem · Answer 3 · 2015-06-09T15:18:41.697

Below should work, providing you pass it a 2 or more column array

Function IsInArray(stringToBeFound1 As String,stringToBeFound2 As String, arr As Variant) As Boolean
    Found1 = 0
    Found2 = 0
    for x=0 to ubound(arr)
        if stringToBeFound1 = arr(x,0) then
            'found
            Found1=1
        end if
        if stringToBeFound2 = arr(x,1) then
            'found
            Found2=1
        end if
        if Found1 =1 and Found2 =1 then IsInArray = TRUE
    next        

End Function

VBA Find a value in a "column" of a 2D array

3 Answers3