excel - Assigning range to array in VBA

Question

I would like to get excel data into an array in VBA, so I do the following:

Dim Arr() As Variant
Arr = ActiveWorkbook.Sheets("Sheet1").Range("C28:R29")

As soon as I run this, I get a type mismatch error 13. I thought the problem was because the 1st row (2 rows in total) represents a string (header) and the 2nd row represents numbers, so I tried to only get one row like this:

Arr= ActiveWorkbook.Sheets("Sheet1").Range("C28:R28")

To no avail, I still get the same problem.

Does anyone know what could be wrong?

Regards Crouz

Answer 1

It's pretty simple, just add .Value (I discovered that trick not so long ago and I'm already fan! :) )

Arr= ActiveWorkbook.Sheets("Sheet1").Range("C28:R28").Value2