Number Items Dynamically with conditions

[kelly mort]

Hi, I use this code to number item in my database. Now I want to take it to the next level; instead of always looking for max and add 1, this time look for sequence. Example for 1, 2, 3, 5. Since 4 is missing, the next item into the database used be indexed 4. Then say I have only 3, 4, 6 in the database, then the numbering for new entries should be 1, 2, 5. The idea is, until those gaps are filled, reserve the max plus 1 rule.

With Sheets("DATA")
Set Drng = .[A2]
Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
        lrRng = Application.Max(.Range(Drng, lrRng.Offset(-1, 0))) + 1
End With

 

[kelly mort]

The OP was about returning the missing value in a column, rather than the highest plus 1.

The OP mentioned nothing about controls or "reg"

I am the OP.
I did mention the controls "reg" in post #5 . For the for loop.

 

[Akuini]

Did you try my code in post #7 ?

 

[kelly mort]

Try this:
This will add all missing number to the next empty row.

[FONT=lucida console][COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, c [COLOR=Royalblue]As[/COLOR] Range

n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A2:A"[/COLOR] & n)
x = WorksheetFunction.Max(r)
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] x
    [COLOR=Royalblue]Set[/COLOR] c = r.Find(What:=i, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]True[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        n = n + [COLOR=crimson]1[/COLOR]
        Cells(n, [COLOR=brown]"A"[/COLOR]) = i
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR][/FONT]

Your code is not doing exactly what I want.

Mikes's code is doing so. My only issue is how to fix my loop inside since it looks different from my original script.

I did the fix in post #6 . I am just waiting for a confirmation to my fix being cool or not. It's working just that I am not sure if that's the best way.

Thanks