Hello!
I'm working on a real bear of a macro and am stuck on a problem.
Part of the report I'm starting with has a name on one row twice (separated by multiple spaces), address on the next twice (separated by multiple spaces), city state zip twice (separated by multiple spaces) on the next row and phone # twice (separated by multiple spaces) on the last row. Sometimes the data isn't exactly the same within each row - for example: the initial name on the row may not be the same as the secondary name on the row-like John Smith Mary Smith. Same situation with the address row, the city/state/zip row and the phone # row.
I need to end up with each "block" of data together on another sheet. I'
On the original data:
Each row has room for a total of 131 characters.
In the "name" row, fields 10-63 are for the initial name, then a true blank spot, then 14 unneeded fields, then the next 53 fields are available for the secondary name on that row.
On the "address" row, fields 10-63 are for the initial address, then a true blank spot, then 14 unneeded fields, then the next 53 fields are available for the secondary address on that row.
NOW COMES THE PROBLEM!
The city state zip for the initial set of data populates fields 10-63 first, then the second instance of city state zip populates fields 79-131
Each city, each state and each zip need to be placed into separate cells of the results. The state is always 2 characters and the zip is always 5 characters and there's always 1 true blank space between city and state and between state and zip. BUT, I never know how long the city name is or if it's 1 word or 2, so I don't know how to tell the macro what criteria to use to break them up. I was thinking ther might be some way to tell it to start at the end, look backwards to the zip and pull those 5 digits to put in the results, then skip a cell (still going backwards), then get the next 2 letters to put in the results, then get everything else as the city.
The macro renames the original sheet as "Data" and creates a new sheet named "Result".
Here's the coding I have so far. It just puts the whole city state zip in each cell on the Result tab because I had to put SOMETHING so I could test it, LOL.
Edited to say that the 2 red lines (that start with "Sheets") successfully put the name and address where I need them to go. The 2 green lines successfully put the second name and address where I need them to go. The rest needs to be corrected to parse out the city state zip into 3 cells for each block of data.
I'm attaching a small sample workbook.Please Login or Register to view this content.
Any help will be GREATLY appreciated!
Jenny
Bookmarks