Array problem: Key words-Variant Array, single-element, type mismatch error

The code below serves to highlight a problem I am confronted with in my project.
Can someone explain why the code generates Type mismatch error when there is only one element in the defined range? It works fine if column A contains more than one populated cell.

Sub VariantArrayA()
Dim u
Dim v


Range("a1") = 100
num = Application.CountA(Range("a:a"))

'create 1st variant array
u = Range("a1:a" & num)

Range("a1:a" & num).Clear


Range("a1") = 500

'create 2nd variant array
v = Range("a1:a" & num)

For i = 1 To num
p = u(i, 1) - v(i, 1) 'Type mismatch error on this line
MsgBox p
Next

End Sub


For comparison, the modified version below generates no error.

Sub VariantArrayB()
Dim u
Dim v


Range("a1") = 100
Range("a2") = 200
num = Application.CountA(Range("a:a"))


'create 1st variant array
u = Range("a1:a" & num)

Range("a1:a" & num).Clear


Range("a1") = 500
Range("a2") = 1000

'create 2nd variant array
v = Range("a1:a" & num)

For i = 1 To num
p = u(i, 1) - v(i, 1)
MsgBox p 'code correctly returns p=-400; p=-800
Next

End Sub

When XL/VBA recognize that there is just one element to be assigned to
the variant, it assigns that value to the variant. In those cases
where the range contains multiple entries, the software creates an
array and assigns it to the variant.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> The code below serves to highlight a problem I am confronted with in my
> project.
> Can someone explain why the code generates Type mismatch error when
> there is only one element in the defined range? It works fine if column
> A contains more than one populated cell.
>
> Sub VariantArrayA()
> Dim u
> Dim v
>
>
> Range("a1") = 100
> num = Application.CountA(Range("a:a"))
>
> 'create 1st variant array
> u = Range("a1:a" & num)
>
> Range("a1:a" & num).Clear
>
>
> Range("a1") = 500
>
> 'create 2nd variant array
> v = Range("a1:a" & num)
>
> For i = 1 To num
> p = u(i, 1) - v(i, 1)* 'Type mismatch error on this line*
> MsgBox p
> Next
>
> End Sub
>
>
> For comparison, the modified version below generates no error.
>
> Sub VariantArrayB()
> Dim u
> Dim v
>
>
> Range("a1") = 100
> Range("a2") = 200
> num = Application.CountA(Range("a:a"))
>
>
> 'create 1st variant array
> u = Range("a1:a" & num)
>
> Range("a1:a" & num).Clear
>
>
> Range("a1") = 500
> Range("a2") = 1000
>
> 'create 2nd variant array
> v = Range("a1:a" & num)
>
> For i = 1 To num
> p = u(i, 1) - v(i, 1)
> MsgBox p 'code correctly returns p=-400; p=-800
> Next
>
> End Sub
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=483027
>
>