PDA

View Full Version : Solved: runtime error 9 when decomposing cells



mehdoush
07-01-2009, 02:29 PM
hey guys,

i have a macro that opens a text file and edit it; when it comes to decomposing part it displays "runtime error 9", here's the code :



Dim Rnng As Range, Dn As Range, oVal, oSp As Long
Dim Ray, cc As Long
Set Rnng = Range(Range("B3"), Range("B" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rnng.Count * 4, 1 To 4)
For Each Dn In Rnng
oVal = Split(Dn.Offset(, 3), "/")
If UBound(oVal) > 0 Then
For oSp = 0 To UBound(oVal)
cc = cc + 1
Ray(cc, 1) = Dn
Ray(cc, 2) = Dn.Offset(, 1)
Ray(cc, 3) = Dn.Offset(, 2)
Ray(cc, 4) = oVal(oSp)
Next oSp
Else
cc = cc + 1
Ray(cc, 1) = Dn
Ray(cc, 2) = Dn.Offset(, 1)
Ray(cc, 3) = Dn.Offset(, 2)
Ray(cc, 4) = Dn.Offset(, 3)
End If
Next Dn

Range("B3").Resize(cc, 4).Value = Ray



help plz

thnks in advance
regards

Bob Phillips
07-01-2009, 03:16 PM
I couldn't force that error. What does your data look like?

mehdoush
07-02-2009, 10:46 AM
here are two files, you'll see the error ;)

text file and a macro.

thanks for being interested!

Bob Phillips
07-02-2009, 12:16 PM
I think the problem is that you resize Ray by 4 times the range count, but sometimes there are 10/12 items per row. Increase it to an arbitrary *20 and it seems to work fine.

mehdoush
07-02-2009, 01:45 PM
hey xld,

the problem is that actually i don't know how to modified it to 20 like you said..
plz help

mehdoush
07-02-2009, 01:59 PM
could someone help me plz ?

Bob Phillips
07-02-2009, 02:55 PM
Change


ReDim Ray(1 To Rnng.Count * 4, 1 To 4)


to


ReDim Ray(1 To Rnng.Count * 20, 1 To 4)

mehdoush
07-03-2009, 01:48 PM
thnks xld it works just fine ......