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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

If column A only contains one populated cell then u and v will not be arrays, they will be variables of the datatype Variant/Double.
Try:

Sub VariantArrayA()
Dim u
Dim v
Dim p
Dim num As Long
Dim i As Long

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)

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

End Sub


Hope this helps
Rowan

davidm wrote: 
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


.

Relevant Pages