Thread: Type mismatch error assigning range to array......

Possibly because you dimmed test_array as an array using ()?

you are correct. If i remove the "()" then it runs fine..........

This is an opportunity to learn here for me.........

Why is this? Why does removing the "()" solve the problem.

Without the (), Excel will assign the type to a variant variable when it is used. By adding the (), you are telling Excel to make the variable a dynamic array with dimensions not yet defined.

thanks guys.

i did some reading as well and while i understand that the "()" means the array will be dynamic what i dont understand (and pardon me if you explained it and i dont understand yet) is if i change the code from

"A2:A2" to "A2:A3" it works.........

i guess this is what i dont understand at this point. I must be missing something. Could it have to do with only being a "single" element in the array vs. more than one?

snb, thanks for the explanation. But one more question.

Why does then removing the "()" cause the type mismatch to go away?



I added this code to the end of the program:

 For x = 1 To UBound(test_array, 1)
    MsgBox (test_array(x, 1))

    Next x

What i found out is that even by removing the "()" what snb said holds true. When i try to use array type referencing like Ubound i get a type mismatch errror.

But it is interesting why by removing the "()" that this does not give a type mismatch errror

test_array = ws.Range("A2:A2").Value2

i have another question: i hope i am not asking to many questions

i have defined this array as follows:

Dim test_array As Variant

And even if i redim it like this

ReDim test_array(1 To 1, 1 To 1)

what snb said holds true.......it converts the single value to a string/number and not an array.......

so what am i to do..........since i cant redim this and make it an array what should i do?

Depends on what you're trying to accomplish, but one possibility is using the IsArray function...

    If IsArray(test_array) Then