Macro to split a single cell data into rows and copying other cells as it is in rows

Hi,

I want to split a cell into rows and copy the rest of the data as it is in the row. For Eg. we have data in rows as (cell A1=Apple, cell B1=Ball, cell C1=Cat, cell D1=Dog, Fish, Rat, cell E1=Elephant). Now I want to split cell D1 in 3 rows(D1,D2,D3) without the commas and copy cell A1 into A2 & A3, B1 into B2 & B3, C1 into C2 & C3 & E1 into E2 & E3 as it is.

This will not be constrained upto single row, there may be multiple rows containing more data in a single cell which needs to be splitted in rows.

First I want to search which cell in the row contains the string with commas and then split it in rows.

I am attaching a excel sheet for your reference.

Regards,
Pankaj

Pankaj Sonawane,

Detach/open workbook Pankaj Sonawane - EF 739727 - SDG10.xls and run macro SplitData.

Thank u for your quick reply Stan,

I did this same thing and now getting where I am wrong. Now my problem is a little different here. I want to search the cell in the row containing string which has commas in it and then apply split to it (It will be like start searching from 1st row if string with commas is present apply split else move to next row, and this will go on till the last row)

After applying the split function I want the macro to copy whole data on next sheet by opening a new worksheet called Sheet2. This is getting a little complicated for me.

I have attached the new worksheet for your reference.

Regards,
Pankaj

Pankaj Sonawane,

Detach/open workbook Pankaj Sonawane - EF 739727 - V2 - SDG10.xls and run macro SplitData.

Hi Stan,

First I would like to take time to thank you for helping me getting the macro in right manner, however I haven't yet succeeded in this.

I am attaching the original sheet which I am working on and below are the details given:

1. I need to split column D (as previously said) and copy rest of the data in the rows as it is.
2. Sheet1: original data
3. Sheet2: Target result to be copied
4. Sheet3: First Macro
5. Sheet4: Second Macro

Below are the problems I am facing while running the macro:

1. When I run the first macro i.e. in Sheet3 where we are saying " If MyC > 0 Then" it is giving me an error on row 18 as given and marked yellow in Sheet3. Also, with error it executes the macro, splits cell D, copies rest of the data as it is in the rows, but does not copy row no 3, 6,9 and so on i.e. it does not copies the row if cell D in that row does not consists comma in it.

2. When I run the second macro i.e. in Sheet4 where we are saying " If MyC >= 0 Then" it is giving me an error on row 16 as given and marked yellow in Sheet4. This second macro executes with error and copies very less data(you will come to know once you execute it at your side)

Result Expected: I want to split the string in cell D whihc consists commas and let rest of the data be copied as it is in the rows. If cell D contains a string/Stmt which contains no comma that whole row should be copied as it is continuing with splitting of data for other rows if commas exists for them.

Hope you get my problem as I have mentioned it as simpler as I can

Regards,
Pankaj

Pankaj Sonawane,

Detach/open workbook Pankaj Sonawane - EF 739727 - V3 - SDG10.xls and run the updated macro SplitDataV3.

Pankaj Sonawane,

Missed that.

Detach/open workbook Pankaj Sonawane - EF 739727 - V4 - SDG10.xls and run macro SplitDataV4.

Pankaj,

Could you please let me know how can I remove all excel sheets from here which I attached for your reference?

Contact one of the Moderators.

Why should they be deleted. You aren't warned to delete any sensitive information before attaching. Removing the files will make it difficult for others to follow the Thread

stealthman,

This original Post has been closed.

You should start your own NEW post, and someone will assists you.