Check If Value Exists In Array Help

I have found some online code that I am trying to incorporate into my procedure, however, am having some difficulties.

The code is as follows

Sub Test()
    Const var As String = "BM01"
    Dim projects As Variant
    Dim x As Long
    projects = Array("BM01", "BM02", "BM03", "BM04")
    On Error Resume Next
    x = WorksheetFunction.Match(var, projects, False)
    If Err = 0 Then
'       **** do something ****
        MsgBox "Found"
    Else
'       **** clear error ****
        Err.Clear
        MsgBox "Not Found"
    End If
    On Error GoTo 0
End Sub

Rather than having projects defined as a hardcoded array, I was hoping to have this pointing to a range.

My range is cells "B12:B16"

When I set projects to equal this range I always get a not found message.

Dim projects as Variant

projects = Range("B12:B16")

Is anyone able to help?

Thanks

McCrimmon

Hello mccrimmon,

Assign the values to the array rather than the cell object . I know that the value of a Range object is the default property, but you must explicitly state this when making an array.

Dim projects as Variant

projects = Range("B12:B16").Value

It works for me without using the Value property (I do agree with Leith that omitting the Value property is sloppy).

Note that when you create the array here:

projects = Array("BM01", "BM02", "BM03", "BM04")

it is a 1-D array.

But when you create the array here:

projects = Range("B12:B16")

it is a 2-D array.

When I set projects to equal this range I always get a not found message.

When you step through your code using F8, after the array of values has been assigned to Projects, can you see BM01 when you expand Projects in the Locals window? If not, perhaps you are pointing at the wrong sheet and should qualify the Range property:

projects = Sheet1.Range("B12:B16").Value