copying ranges to a variant

[jimrobinson]

I am referring to http://support.microsoft.com/kb/213798

and have the following variation on the theme:

Public Sub Demo()
Dim i As Integer
Dim htable As Variant
Dim h As Range
Dim ws As Worksheet
Set ws = Workbooks("Demo").Worksheets("Blocks")
Set h = ws.Range(ws.Cells(2, 1), ws.Cells(7, 4))


Set htable = h.Value

For i = 1 To UBound(htable, 1)
If IsNumeric(htable(i, 4)) Then _
Debug.Print htable(i, 1)
Next i

End Sub

The highlighted line gets error: Type Mismatch

 

[Firefly2012]

h.Value returns an array not an object so you just need to drop the Set:

hTable = h.Value

 

[jimrobinson]

Worked like a charm
Thanks