Range to Array
 

Why does:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A2")
v = r.Value
End Sub

work just fine, but:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A1")
v = r.Value
End Sub

raise a type mismatch error?
--
Gary''s Student - gsnu200727

Range to Array
 

Hi Gary,

In each of your subs you have declared v as a
dynamic array.

From the Immediate window:

?Typename(range("A1:A2").Value)
Variant()

?Typename(range("A1:A1").Value)
String.




---
Regards,
Norman



"Gary''s Student" wrote in message
...
Why does:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A2")
v = r.Value
End Sub

work just fine, but:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A1")
v = r.Value
End Sub

raise a type mismatch error?
--
Gary''s Student - gsnu200727

Range to Array
 

"Gary''s Student" skrev i en
meddelelse ...
Why does:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A2")
v = r.Value
End Sub

work just fine, but:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A1")
v = r.Value
End Sub

raise a type mismatch error?
--
Gary''s Student - gsnu200727

Hi

Because you have defined v() as an array,
and Range("A1:A1") only contains one cell,
so v=r.Value will return the type mismatch error,
because Excel doesn't consider v an array in
this situation.

If you had Dim v
it would work in both instances, but in the first
instance v would be an array, while v in the second
instance is a variant variable!

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Range to Array
 

Hi Gary,

Just to add,

?IsArray(Range("A1:A2"))
True

? Isarray(Range("A1"))
False


---
Regards,
Norman

Range to Array
 

In your second example you are trying to assign a single value to an
undimensioned variant array, it will fail.
Declare ' v ' without the brackets, then it can accept either a single value
or an array of values. You could test IsArray(v) or r.count 1.

If you need ' v ' to be a 2d array for consistency you could do this
(untested)

Sub qwerty3()
Dim r As Range
Dim v() ' redundant
Set r = Range("A1:g2")
With r
ReDim v(1 To .Rows.Count, 1 To .Columns.Count)
End With
If r.Count = 1 Then
v(1, 1) = r.Value
Else
v = r.Value
End If

Debug.Print UBound(v), UBound(v, 2)
End Sub

Regards,
Peter T

"Gary''s Student" wrote in message
...
Why does:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A2")
v = r.Value
End Sub

work just fine, but:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A1")
v = r.Value
End Sub

raise a type mismatch error?
--
Gary''s Student - gsnu200727

Range to Array
 

Thanks to all of you.
Just another Dim mistake.

(or was I being Dim witted??)
--
Gary''s Student - gsnu200727


"Peter T" wrote:

In your second example you are trying to assign a single value to an
undimensioned variant array, it will fail.
Declare ' v ' without the brackets, then it can accept either a single value
or an array of values. You could test IsArray(v) or r.count 1.

If you need ' v ' to be a 2d array for consistency you could do this
(untested)

Sub qwerty3()
Dim r As Range
Dim v() ' redundant
Set r = Range("A1:g2")
With r
ReDim v(1 To .Rows.Count, 1 To .Columns.Count)
End With
If r.Count = 1 Then
v(1, 1) = r.Value
Else
v = r.Value
End If

Debug.Print UBound(v), UBound(v, 2)
End Sub

Regards,
Peter T

"Gary''s Student" wrote in message
...
Why does:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A2")
v = r.Value
End Sub

work just fine, but:

Sub qwerty()
Dim r As Range
Dim v()
Set r = Range("A1:A1")
v = r.Value
End Sub

raise a type mismatch error?
--
Gary''s Student - gsnu200727