Macro to add missing numbers in a sequence

**webfeet2** · 03-13-2012, 10:25 PM

Hello,

I have a long list of alphanumeric values (i.e. BB235) that is comprised of multiple value sequences. For example, one sequence may be C3345 to C3399 and then the sequence will jump to JB900 to JB979. I would like to design an excel macro that would automatically insert rows and the missing values in a sequence based on rows that I highlight.

Any tips or advice would be much appreciated, even if it is only to tell me that it cannot be done.

**NickyC** · 03-13-2012, 11:16 PM

can I confirm - if say you selected in cell A1 value BB235, and in cell A2 value BB245, you would like to insert 9 rows between them and add the values BB236 to BB244

also - does every alphanumeric value beging with letters and end with numbers?

**NickyC** · 03-13-2012, 11:38 PM

.. if so try this

it will only work if you have two cells selected and both cells' contents beging with the same letters (e.g. MM22 MM45, not MA22 MM45).

Please Login or Register  to view this content.

**webfeet2** · 03-26-2012, 01:37 PM

Thank you so much NickyC. FYI, when I run this, it inserts only the numbers of the missing sequences, not the text and the numbers. For example, if the sequence skips from RR54 to RR57, the macro will insert rows and the numbers
Do you know how I could adjust this so that the text would be inserted as well?

**NickyC** · 04-02-2012, 06:00 AM

apologies for not replying sooner, I have been away.

This might solve your problem:

Please Login or Register  to view this content.

**vendam** · 07-10-2015, 05:29 AM

Hello Mr. NickyC,
I would like to kindly please you for help.
Code above is little bit similar what I need.
Do you have solution for problem below please?

For better understanding what code below mean:
ABCDE5055153201001:
ABCDE - letter to recognize producer (still same)
50 - numbers still same, mean also producer
55153201 - dispatch note number (55 at the start is always)
001 - number of package in dispatch note

For example in columm A are these datas:

ABCDE5055153201001
ABCDE5055153201002
ABCDE5055153201003
ABCDE5055153201006
ABCDE5055153201008
ABCDE5055154201001
ABCDE5055154201002
ABCDE5055154201004
ABCDE5055156205001
ABCDE5055156308001
ABCDE5055156308002

Is there possible to find macro code for finding missing sequence in evere unique basic code.
There has to be said, that every specific base code has to start with last 3 numbers „001“ (mean package one). Maximum for finding highest sequence is done by founded maximum in 3 last numbers founded in columm.

After processing macro it should be visible in columm B:

ABCDE5055153201004
ABCDE5055153201005
ABCDE5055153201007
ABCDE5055154201003

In fact that mean, that we forgot to load 4 boxes above (55153201004, 005, 007 and 55154201003).

There should be also checked, if there are not for example only two codes, but without „001“ at the end.
ABCDE5055153201002
ABCDE5055153201003

Thank you very much for help.
vendam, Czech R.

**HaHoBe** · 07-10-2015, 10:34 AM

Hi, vendam,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Ciao,
Holger