Loop rows and columns

[twomind]

Hi,

I needed to do something in excel that only can be done with macros, but I had no idea, so I started to study macros last week and tried to do it, but it's definitely too much for me so need your help please:

I have a report in which some cells have 2-3-4 values separated by "/", and I need that a new row is inserted for each of them and the rest of the cells in the row that had only 1 value are now copied in the new inserted rows, so that where I had 1 row with 3 values in same cell now I have 3 rows with each value in one row and the rest of cells the same.

So I managed to create a macro that checks the first row until it finishes and insert the new row when it encounters the "/", but I don't know how to continue with:

- Make it insert the number of rows depending on the number of values separated by "/" in the cell (I only did it for 1).
- Make it start with first row, and once it's finished, continue with the second row, third, etc... til the end, inserting new rows every time it finds the mentioned situation.
- Sometimes there will be 2 cells in the same row with this "problem", but always with the same number of values within it. In these cases I need that the same split is done for the second cell with "/", like with the first one.

Please find attached the file with an example including the macro I tried.

Thanks a lot in advance

[jaslake]

Hi twomind

This code is in the attached. It appears to do as you require. Let me know of issues.

Please Login or Register  to view this content.

[twomind]

Hi twomind

This code is in the attached. It appears to do as you require. Let me know of issues.

Please Login or Register  to view this content.

Thank you very much, it works perfectly as I wanted. Just one more thing, I see that it works for columns C and G, it's possible that in near future I will have more columns with "/", Would it be possible to do it so that it works with every column that it finds with "/"?

Thanks a lot

[jaslake]

Hi twomind

Currently Column C is the first Column that contains "/" and it contains the MOST "/". The code I've written relies on the fact that Column C contains the MOST "/".

So, regardless of which Column is the first Column to contain "/", will it contain the MOST "/".

Please get back to me.

[jaslake]

Hi twomind

This code has limitations

Please Login or Register  to view this content.

I don't know the anomalies of your data. The code looks for Columns that have "/" in them and will deal with those columns. The single most important is the first column the code finds. All is based off the rows in the first found column. The code won't fail but it'll give false results if any column in the row has more "/" than the first found column.

For example: pass_fail.jpg

Try it...let me know.

[twomind]

Thanks Jaslake,

Now it works perfectly well. Regarding what you mention, no problem because all the cells in a row must always have the same number of "/".

Thank you very much, solution is brilliant and helps me a lot

Best regards

[jaslake]

Hi twomind
You're welcome...glad to be of help.