Not sure why you need an array:

If Application.CountIf(Range("G2:G1000"), Range("H1")) Then
MsgBox "In the range"
Else
MsgBox "Not found"
End If

But if you really want the range values in an array, assign them to
a variant variable:

Dim myArr As Variant
Dim i As Long
myArr = Range("G2:G100").Value
For i = LBound(myArr) To UBound(myArr)
'Do something
Next i


In article ,
"Robert" wrote:

I need to create a macro that will use a range G2:G1000 as
an array then check a cell value to find out if it is in
that array.

Any help is appreciated

Thank you in advance
Robert