0

I'm trying to extract a worksheet range to be used in an array variable. However, whenever I try to run the code I get a "Run Time Error '13': Type Mismatch" message. I think that my problem is that the range I am trying to assign to the array has no type, but I can't figure out how to get around this. Could I please get some pointers on what I'm missing? Thanks

Sub GetVal()

Dim Number As Long

Worksheets("Sheet1").Activate   'set sheet1 as active worksheet

'get Number from sheet
Number = CLng(Range("N").Value) 'Cell N is named on sheet

'declare other variables
Dim matrix() As Long
ReDim matrix(1 To Number, 1 To 3)
Dim Row As Long
Row = Number + 3


'get data from sheet
matrix = Range("C4:E" & Row).Value
End Sub
user3263945
  • 3
  • 1
  • 1
    See [this question](http://stackoverflow.com/q/21386768/11683), listed in the "related" section on the right. – GSerg Feb 02 '14 at 22:05
  • I can't really pinpoint the problem since some pieces of the puzzle are left unknown. What i think you should do is make sure your values are correct and the dimension of your array is correct. If the dimensions are different, that might cause the error. – L42 Feb 03 '14 at 02:40
  • 1
    What is this `Number = CLng(Range("N").Value)` line supposed to? Why are you trying to convert a variant array (`Range("N").Value`) to a `Long` data type? See [**THIS**](http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730) –  Feb 03 '14 at 08:07
  • Thanks for the replies everyone. I know where I was going wrong now. mehow, I was using CLng to try and keep the range.value consistent with how I had defined the type of the variable 'Number'. But I realise it is not as simple as that now. – user3263945 Feb 03 '14 at 22:49

1 Answers1

0

Change

Dim matrix() As Long

to

Dim matrix() As Variant

or 

Dim matrix As Variant

For some reason that I don't know, Excel does not let you specify a data type when assigning an array from a Range. You either need an array of Variants or a Variant holding an array.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73