Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time 9 Subscript ouf of Range Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time 9 Subscript ouf of Range Error
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... -- HTH... Jim Thomlinson "Excel Monkey" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time 9 Subscript ouf of Range Error
Yes that definately works. I can't figure out why mine does not when you
length the variable. Thanks EM "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Excel Monkey" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time 9 Subscript ouf of Range Error
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time 9 Subscript ouf of Range Error
Yup I see it - confusing to say the least!
Thanks EM "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 9 - Subscript out of range | Excel Programming | |||
Run time error 9 (Subscript Out Of Range) | Excel Programming | |||
run-time error '9': Subscript out of range - WHY?? | Excel Programming | |||
Run time error-subscript out of range | Excel Programming | |||
Run time error '9' Subscript out of range | Excel Programming |