Split comma separated entries to new row

Hey everybody,

I almost found what I am looking for in this thread (see link) as I was trying to split comma separated entries in some column to a new row.
This means that for each row in which there is a comma separated list in column P with, say 5 entries, there should be generated 4 other rows below such that eventually in each row in this column there is one entry. As for the other cells in each of those 4 rows, the cells should just be copied.

http://www.excelforum.com/excel-prog...ml#post3590704

I managed to adjust the code from above:

Please Login or Register  to view this content.

However, there is one feature missing for my spreadsheet: In column O I need to number each of the entries of the list.
So suppose there is a list of 4 entries in some row. Now I need the first row in column O to have a 1, the second to have a 2 etc.

I would very much appreciate your help.
Thanks a lot!
trefix

Unfortunately it generates an Error 1004: application or object-defined error.
Sorry, I don't know if that's the correct translation cause I am using it in German

Please Login or Register  to view this content.

CCell is defined as a Range. Ranges must use "Set"

You are correct. I missed the earlier one.

Thank you guys, especially Kaper! The code works perfectly fine! Now I can go on working on it using STATA
I am afraid the wrong line stems from my early attempts to resolve the issues myself. I am sorry for the confusion! But well, you found it out anyway

Thanks & Cheers!