How arrange from one cell to column.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geniusideas - 19 May 2007 16:59 GMT

Example :
Before :
Part name Qty Location (seperated by comma)
Button 1 2 B1,B2
Button 2 3 B4,B5,B6

After
Button 1 1 B1
Button 1 1 B2
Button 2 1 B4
Button 2 1 B5
Button 2 1 B6

Pls help How to do using VBA code.
For the simple as above no problem, the problem is in Excal we only
have 256 column but sometime my qty is more.
Pls help

http://microsoft-excel-macro.blogspot.com

Reply to this Message

Sebation.G - 20 May 2007 04:07 GMT

TRY: this code can get your result,but it has a defult that it will take
long time when it does with huge data.
Hope this can be helpful.
Sub test()
Dim i As Integer
Dim arr() As String
Application.ScreenUpdating = False
On Error Resume Next

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arr = Split(Cells(i, 3), ",")
For J = 0 To UBound(arr)
With Worksheets(2)
LASTROW = .Cells(Rows.Count, 3).End(xlUp).Row + 1
.Cells(LASTROW, 1) = Cells(i, 1)
.Cells(LASTROW, 3) = arr(J)
End With
Next J
Next i
Application.ScreenUpdating = True
End Sub

Signature

Regards,

Sebation.G
"geniusideas" <geniusideas@gmail.com>
??????:1179590354.361017.259920@p77g2000hsh.googlegroups.com...

> Example :
> Before :
[quoted text clipped - 15 lines]
>
> http://microsoft-excel-macro.blogspot.com

Reply to this Message

geniusideas - 20 May 2007 07:25 GMT

> TRY: this code can get your result,but it has a defult that it will take
> long time when it does with huge data.
[quoted text clipped - 44 lines]
>
> >http://microsoft-excel-macro.blogspot.com

Tq, it goes to sheet 2, but i need it in same sheet, How?

Reply to this Message

Sebation.G - 20 May 2007 08:50 GMT

Sub test()
Dim i As Integer
Dim arr() As String
Application.ScreenUpdating = False
On Error Resume Next

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arr = Split(Cells(i, 3), ",")
For j = 0 To UBound(arr)
LASTROW = Cells(Rows.Count, 6).End(xlUp).Row + 1 'change it as your
destination
Cells(LASTROW, 4) = Cells(i, 1)
Cells(LASTROW, 5) = 1
Cells(LASTROW, 6) = arr(j)

Next j
Next i
Application.ScreenUpdating = True
End Sub

hope this can be helpful

Signature

Regards,

Sebation.G
"geniusideas" <geniusideas@gmail.com>
??????:1179642304.407734.187100@o5g2000hsb.googlegroups.com...

>> TRY: this code can get your result,but it has a defult that it will take
>> long time when it does with huge data.
[quoted text clipped - 46 lines]
>
> Tq, it goes to sheet 2, but i need it in same sheet, How?

Reply to this Message

geniusideas - 20 May 2007 09:09 GMT

> Sub test()
> Dim i As Integer
[quoted text clipped - 74 lines]
>
> > Tq, it goes to sheet 2, but i need it in same sheet, How?

Tq very much, it works..

Reply to this Message