Split cell contents over multiple rows based on cell contents

**naigy** · 01-16-2011, 01:49 AM

Hi All,

I have my code for the most part working but unfortunately it duplicates and misses some data. I have tried to find the issue but I just can't see it and thought having some fresh eyes looking at it may be able to sort it out. Basically the source data is the orders which were sent on a particular delivery docket. The letters are not important so I have that data stripped out. I then were there were multiple orders on a line such as KC224601-12 needs to become 224601 and on the next line 224612.

Below is some sample data along with the results it gives me along with my desired results. This has not formatted properly but is also visible on the second sheet of the attached file.

HTML Code:

I have attached my spreadsheet for looking over. The part which I think is causing the error is in the code snip below which is the section which looks after the multiple orders section. This is just a snippet of the whole macro.

Please Login or Register  to view this content.

Any assistance or advice people can give would be much appreciated as it is starting to drive me nuts and I know it is probably a really simple thing I am missing.

**watersev** · 01-16-2011, 05:19 AM

hi, I would suggest to do that a bit differently, press "Start" on Sheet "Source"

**naigy** · 01-16-2011, 03:05 PM

Hi Watersev,

You have sure condensed by code to a very short process which for the most part looks like it has worked well however there is one issue with it.

Please Login or Register  to view this content.

I think this is due to it somehow always referring to the first order. What the code needs to do is refer to the order before the current one and just change the last x number of characters (where x is the number of numbers before the next "-" or the end of the line).

As I don't follow your code as it is using functions I haven't used before in Excel or Access vba would appreciate your assistance to sort this.

Thanks for your prompt reply and assistance.

**watersev** · 01-16-2011, 03:10 PM

I see your point, see attachment

**naigy** · 01-17-2011, 05:38 PM

Sweet. I think that has sorted it. I have modified the code a little to try and adapt it into the rest of the worksheet and so far so good. Will post back if I have any further issues.

The way you have coded this is really interesting and I look forward to steping through it in close detail so i can understand exactly how this works as i would really like to improve my abilities in this area.