CODEWITHSUNDEEP
arraysexcelvba
nwhaught
nwhaught

Reputation: 1592

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

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.

Upvotes: 1

Views: 12330

Answers (3)

nwhaught
nwhaught

Reputation: 1592

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?

Upvotes: 1

Dawid
Dawid

Reputation: 786

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

Upvotes: 2

99moorem
99moorem

Reputation: 1983

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

Upvotes: 0

Related Questions