Split & Rearrange number
On Thu, 27 Dec 2007 18:37:06 +0800, "Max" wrote:
Ron, thanks. Noted.
My earlier response crossed your follow up here.
Max,
Further investigation reveals that the problem seems to be a 1024 character
limitation in the .text property of the range object.
Please try this routine instead, on your data, and let me know how it works.
================================================== ==
Sub Rearrange()
Dim c As Range
Dim re As Object, mc As Object
Dim str As String
Dim i As Long
Const sPat As String = "\w+"
Dim ColCt As Long
ColCt = Columns.Count
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = sPat
End With
For Each c In Selection
str = c
If re.test(str) = True Then
Set mc = re.Execute(str)
For i = 0 To mc.Count - 1
c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i)
Next i
End If
Next c
End Sub
================================================== ========
--ron
|