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

R3uK · Accepted Answer · 2015-04-15 08:18:24Z

5

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

edited Apr 15, 2015 at 8:18

answered Apr 13, 2015 at 14:08

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

stucharo Over a year ago

I have never understood this. Arr=Range("C28:R28") will work but Arr= ActiveWorkbook.Sheets("Sheet1").Range("C28:R28") does not. Don't both variations return a Range object? Any thoughts?

R3uK Over a year ago

A lot of but no useful I'm afraid, I never used the Array=Range so I don't really now but it might have to do with the data type (numerical, text, ...) that is in your range. But I can't help you more that this!

brettdj Over a year ago

When putting ranges in arrays use Value2 rather than Value. Charles Williams explains this well at his blog.

R3uK Over a year ago

Ok, jhere is the link : fastexcel.wordpress.com/2011/11/30/… And to sum up, .Value convert some of your data to keep dates and such, and cut your number with a 4 digits accuracy, and .Value2 is faster because it doesn't convert anything and so it can't threaten your data integrity!

Collectives™ on Stack Overflow

Assigning range to array in VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related