Checking if Value is Present in an Array

Question

I modified the VBA code found at checking if value present in array to the code below. A user will choose a field name and I want the code to find the column that field is listed in and create an array of all items in that column, but I want each non-blank value to show up only once in the array. I don't want any duplicates.

For example, if the column has values A, B, A, C, D, I want the code to return the array as A, B, C, D.

I get a run time error 13 - Type mismatch on this line of the code:

If cell.Value <> "" And IsError(WorksheetFunction.Match(cell.Value, MyArray, 0)) Then

I'm confused, because it seems like all my data types are correct. Can anyone offer any help?

Sub ChangeBlock()

Dim MyArray() As String
Dim cell As Range
Dim ColNum As Integer
Dim i As Integer

If Not Intersect(ActiveCell, Range("Block1")) Is Nothing Then
    If ActiveCell.Value = "" Then Exit Sub

ColNum = WorksheetFunction.Match(ActiveCell.Value, Sheets("Budget Table").Range("A1:AG1"), 0)

    For Each cell In Sheets("Budget Table").Columns(ColNum)
        If cell.Value <> "" And IsError(WorksheetFunction.Match(cell.Value, MyArray, 0)) Then
            ReDim Preserve MyArray(i)
            MyArray(i) = cell.Value
            i = i + 1
        End If
    Next
End If

MsgBox (MyArray)

End Sub

Answer 1

To use IsError, use Application.Match instead of WorksheetFunction.Match.

Although these two methods are similar, the way they handle errors is slightly different. The former returns an error variant that you can test with IsError, while the latter just throws an error that you can only catch with an error-handling mechanism.

Finally, if your data is large, this is not the optimal way to do the checking for duplicates; you should think of using a Dictionary.

Answer 2

The Error 13 Type mismatch is caused here:

cell.Value <> ""

The Value here is 2D-array something like (1 To 1048576, 1 To 1) and it is not possible to compare this array to a string hence the type mismatch.

Edit:

---

Actually the variable cell is a column so to compare properly it is necessary to say what element of the array is compared, e.g. for the first element:

cell.Value()(1, 1) = ""

More correct would be to rename the variable cell to e.g. oneColumn because the variable cell actually contains a reference to a column, e.g. like this:

Dim myColumns As Range
Set myColumns = Sheets("Budget Table").Columns(ColNum)

Dim oneColumn As Range

For Each oneColumn In myColumns
    ' ...
Next oneColumn

Note: Here the for-each does not make sense because myColumns references just one column and myColumns.Columns.Count returns 1. So all you wanted was actually myColumns.Cells which returns all the cells of the column.

The value of oneColumn is 2D-array and to be honest I don't know why it is 2D and not just 1D either. When you would examine Value of e.g. Range("A1:C3") then you see it returns 2D-array which is understandable. But why one column of columns returns 2D as well? Seems to be odd to me as well :). An example of 1D-array would be Dim oneDArray: oneDArray = Array("A", "B", "C"). As far as I know 1D-array is never returned from a Range.Value property. Here interesting article about array dimensions in VBA.

---

But this is not necessary because each Range has a property Cells. So here the Columns(ColNum).Cells should be used.

The complete code could look something like the following but it is a little bit too complicated. First the array has a lot of empty elements because the whole column is used and second consider the solution with a dictionary like @A.S.H is proposing. HTH

Dim MyArray() As String
Dim cell As Range
Dim ColNum As Integer
Dim i As Integer

ReDim MyArray(0 To 0)

With Sheets("Budget Table")

    If Intersect(ActiveCell, .Range("Block1")) Is Nothing Then _
        Exit Sub

    If ActiveCell.Value = "" Then _
        Exit Sub

    ColNum = Application.Match(ActiveCell.Value, .Range("A1:AG1"), 0)

    For Each cell In .Columns(ColNum).Cells
        If cell.Value = "" Then _
            GoTo continue

        If IsError(Application.Match(cell.Value, MyArray, 0)) Then
            If i > 0 Then
                ReDim Preserve MyArray(i)
            End If

            MyArray(i) = cell.Value
            i = i + 1

        End If
continue:
    Next cell

End With