Split cell data in mutiple rows
Hi, I had a bit of a play round, unfortunately time forbids.. However the
following macro works except for a slight bug. Try it using the Macro "Step
Into" and you will see the gist of it then you can probably fix the error and
do a cleanup in the work column I use.
Good luck.
Sub Test()
'
'
'
Range("C2").Select
ActiveCell.Offset(0, 2) = "=Find("","",C2)"
On Error GoTo NoComma
Do Until ActiveCell = ""
If ActiveCell.Offset(0, 2) 0 Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Copy
ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 2).Select
x = ActiveCell.Offset(0, 2)
y = Len(ActiveCell)
z = y - x
x = x - 1
ActiveCell = Left$(ActiveCell, x)
ActiveCell.Offset(1, 0).Select
ActiveCell = Right$(ActiveCell, z)
ActiveCell.Offset(-1, 0).Select
End If
NoComma:
ActiveCell.Offset(1, 2).Select
ActiveCell.Offset(-1, 0).Copy
ActiveSheet.Paste
ActiveCell.Offset(0, -2).Select
Loop
End Sub
" wrote:
Hello all,
I have a excel worksheet as :
Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q
But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like
Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q
Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible
Thanks
|