Hello,
I am trying to code a solution for a problem I have.
In column A of my spreadsheets I have blocks ID strings that are all made up of two letters and five digits (ex: AB12345). Each ID appears at least on one row (but usually more), then there is a blank and the next ID starts. The attached file shows full examples.
Correct:
AB12345
AB12345
AB12345
BLANK
AB12346
BLANK
AB12347
AB12347
Incorrect:
AB12345
AB12345
AB12345
BLANK
AB12344 (this should be pointed out as an error)
BLANK
BLANK (double blanks should also be pointed out)
AB12347
AB12347
I want to make sure that the ID numbers count up properly, so I want the macro to point out where the numbers skip (ex from AB12345 to AB12347).
Each ID should be followed by one that is one number larger (so after AB12345 you have AB12346).
Basically the code should:
- For all cells with data in column A (starting with A2)
- While the current cell is not a blank make sure that all ID numbers are the same until you hit a blank (if not output the row where the error occurs)
- When you hit a blank cell make sure that the cell under the blank is one number bigger than the ID above the blank (if not output the row where the error occurs). Also, if there are two blanks in a row the macro should point it out too.
That is all, it sounds easy, but I made a mess when trying to code it, and I am sure that there must be an easier way to do what I am trying to do.
Any help would be great. Thanks everyone!
Bookmarks