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
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