Run-Time 9 Subscript ouf of Range Error

[Excel Monkey]

The following routine is not working when I expand the variable h. I have
tried changing these to String variable and I get the same error (Run-Time 9
Subscript ouf of Range). Why is this?

Sub Test()
Dim M As Double
Dim h As Variant
Dim k As Variant

h = "4:4,3:3" '< this works but will not if "4:4,3:3,2:2"

If Not UBound(Split(h, ",")) = 0 Then
For M = 0 To UBound(Split(h, ","))
If M = 0 Then
k = k & Split(Split(h, ",")(M), ":")(M)
Else
k = k & ", " & Split(Split(h, ",")(M), ":")(M)
End If
Next
h = k
End If

End Sub

Thanks

EM

 

[Jim Thomlinson]

Here is my take on it...

Sub Test()
Dim M As Long
Dim h As Variant
Dim k As Variant
Dim ary As Variant

h = "4:4,3:3,2:2" '< this works but will not if "4:4,3:3,2:2"

ary = Split(h, ",")
For M = LBound(ary) To UBound(ary)
k = k & Left(ary(M), 1) & ":"
Next M
k = Left(k, Len(k) - 1)
MsgBox k

End Sub

You need to traverse through the array created by the split...

 

[Excel Monkey]

Yes that definately works. I can't figure out why mine does not when you
length the variable.

Thanks

EM

 

[Dave Peterson]

When h = "4:4,3:3,2:2", you'll get 3 elements from the first split (indices 0,
1, 2)

And with M = 2, this will fail:
k = k & ", " & Split(Split(h, ",")(M), ":")(M)
Because 2:2 only has two elements (indices 0 and 1).

Maybe you wanted:
k = k & ", " & Split(Split(h, ",")(M), ":")(1)
or
k = k & ", " & Split(Split(h, ",")(M), ":")(0)

 

[Excel Monkey]

Yup I see it - confusing to say the least!

Thanks

EM