current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
1  
See this question, listed in the "related" section on the right. –  GSerg Feb 2 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 3 at 2: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 –  vba4all Feb 3 at 8: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 3 at 22:49

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Thank you Dick. Still getting used to VBA and the Variant type. Got it working as I want now though. –  user3263945 Feb 3 at 22:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.