macro to split text in columns

[nshanmugaraj]

Hi
I need to split text by an macro into respective colums..

DATE FINAL DATE NAME AMT1 AMT2 VALUE

3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00
3/31/2003 6/02/2003 HARI 7.220 15.00 693.00
3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00

and so on...

the record in column A.
This should be splited to the respective colums as per the click of button and the sum of the values also needed.

i suggest the persons to go by Data-> Text to Columns Wizard. But they say they will paste the text and then click a button ,the work to be done .

PLEASE HELP

[Toppers]

An example of code to split data; input on Sheet1, output to Sheet2

Dim v As Variant
Dim r As Long, i As Integer
With Worksheets("Sheet1")
For r = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
v = Split(.Cells(r, "A"), " ")
For i = LBound(v) To UBound(v)
Worksheets("Sheet2").Cells(r, i + 1) = v(i)
Next i
Next r
End With


HTH

"nshanmugaraj" wrote:

>
> Hi
> I need to split text by an macro into respective colums..
>
> DATE FINAL DATE NAME AMT1 AMT2 VALUE
>
> 3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00
> 3/31/2003 6/02/2003 HARI 7.220 15.00 693.00
> 3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00
>
> and so on...
>
> the record in column A.
> This should be splited to the respective colums as per the click of
> button and the sum of the values also needed.
>
> i suggest the persons to go by Data-> Text to Columns Wizard. But they
> say they will paste the text and then click a button ,the work to be
> done .
>
> PLEASE HELP
>
>
> --
> nshanmugaraj
> ------------------------------------------------------------------------
> nshanmugaraj's Profile: http://www.excelforum.com/member.php...o&userid=31570
> View this thread: http://www.excelforum.com/showthread...hreadid=518683
>
>

[Toppers]

An example of code to split data; input on Sheet1, output to Sheet2

Dim v As Variant
Dim r As Long, i As Integer
With Worksheets("Sheet1")
For r = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
v = Split(.Cells(r, "A"), " ")
For i = LBound(v) To UBound(v)
Worksheets("Sheet2").Cells(r, i + 1) = v(i)
Next i
Next r
End With


HTH

"nshanmugaraj" wrote:

>
> Hi
> I need to split text by an macro into respective colums..
>
> DATE FINAL DATE NAME AMT1 AMT2 VALUE
>
> 3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00
> 3/31/2003 6/02/2003 HARI 7.220 15.00 693.00
> 3/31/2003 6/02/2003 THOMAS 4.950 15.00 693.00
>
> and so on...
>
> the record in column A.
> This should be splited to the respective colums as per the click of
> button and the sum of the values also needed.
>
> i suggest the persons to go by Data-> Text to Columns Wizard. But they
> say they will paste the text and then click a button ,the work to be
> done .
>
> PLEASE HELP
>
>
> --
> nshanmugaraj
> ------------------------------------------------------------------------
> nshanmugaraj's Profile: http://www.excelforum.com/member.php...o&userid=31570
> View this thread: http://www.excelforum.com/showthread...hreadid=518683
>
>