Filling missing IDs in column

[Jacob_]

Hi,
can anyone help me with this?

I have in column IDs they look like this:
A0002
A0010
A0003
Not sorted in any way and some of them are missing. I need to find the blank cells and fill it with what's missing.
It has to be done using vba.
Thanks.

 

[James006]

Hi,

With your data in range A1:A3 ... you can test in cell B1 The following array formula :

=IFERROR("A000"&SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))&":"&MAX(VALUE(SUBSTITUTE($A$1:$A$3,"A",""))))),VALUE(SUBSTITUTE($A$1:$A$3,"A","")),0)),ROW(INDIRECT(MIN(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))&":"&MAX(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))))),ROWS($1:1)),"")

Hope this will help