Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split cell data in mutiple rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split cell data in mutiple rows
TRY ...
Sub CostC() Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") Dim lasrow As Long, r As Long, rr As Long rr = 1 ws1.Cells(1, 1).Resize(1, 4).Copy ws2.Cells(rr, 1) With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row For r = 2 To lastrow ns = 1 Do rr = rr + 1 ws1.Cells(r, 1).Resize(1, 4).Copy ws2.Cells(rr, 1) n = InStr(ns, .Cells(r, 3), ",") If n < 0 Then ws2.Cells(rr, 3) = Mid(.Cells(r, 3), ns, n - ns) ns = n + 1 Else ws2.Cells(rr, 3) = Mid(.Cells(r, 3), ns, 255) End If Loop Until n = 0 Next r End With End Sub HTH " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query doesn't add/delete data in rows on refresh | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Cell data format | Excel Discussion (Misc queries) | |||
Combining rows of data that have one cell in common | Excel Discussion (Misc queries) | |||
Split data from one cell to two separate cells | Excel Worksheet Functions |