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

 

[mikerickson]

This brute force approach will work

Dim i as Long

I = 0
With Sheets("Data").Range("A:A")
    With Range(.Cells(2,1), .Cells(Rows.Count, 1).End(xlUp))
        Do
            I = I + 1
        Loop Until IsError(Application.Match(I, .Cells, 0))
        .Offset(.Rows.Count,0).Cells(1,1).Value = I
    End With
End With

 

[kelly mort]

I get runtime error 424
Object required

Then highlights

With Range(.Cells(2,1), .Cells(Rows.Count, 1).End(xlUp))


' I changed .cells (Rows.Count, 1) to .cells (.Rows.Count, 1) before getting that error



Also with Range to with .Range

Because I had alerts they were not defined variables.

 

[mikerickson]

It worked for me without alteration.
The lack of a dot before the Range in the With line is deliberate. As is the lack of a dot before Rows.Count (although that should have no effect)

Have you tried it as posted?

 

[kelly mort]

Sorry that was a typo from my end. Missed an s.

I am using a for loop with this line.

For c = 1 To 10
LrRng.Offset(0, c ).value = Controls("reg" & c + 1).value 
Next c

that offset line in your code is not familiar to me.

Can you place that line in your code for me?

Regards

 

[kelly mort]

Dim i as Long

I = 0
With Sheets("Data").Range("A:A")
    With Range(.Cells(2,1), .Cells(Rows.Count, 1).End(xlUp))
        Do
            I = I + 1
        Loop Until IsError(Application.Match(I, .Cells, 0))
        .Offset(.Rows.Count,0).Cells(1,1).Value = I

        Dim c As Long 
        For c = 1 To 10
          .Offset(.Rows.Count,c).Cells(1,1).Value = Controls("reg" & c + 1).Value 
        Next c 

    End With
End With

This is how I placed the loop in the code. Let me know if that's okay. It's running though

Thanks

 

[Akuini]

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]

 

[mikerickson]

Perhaps

Sub test()
    Set LrRng = Sheets("Data").Range("A2")
    For c = 1 To 10
        With LrRng.EntireColumn
            .Cells(.rows.count, 1).End(xlUp).Offset(1,0).value = NextNumber()
        End With
    Next c
end Sub

Function NextNumber() as Long

Dim i as Long


With Sheets("Data").Range("A:A")
    With Range(.Cells(2,1), .Cells(Rows.Count, 1).End(xlUp))
        I = WorksheetFunction.Min(.Cells)
        Do
            I = I + 1
        Loop Until IsError(Application.Match(I, .Cells, 0))
        .Offset(.Rows.Count,0).Cells(1,1).Value = I
    End With
End With

NextNumber = I

End Function

 

[kelly mort]

Perhaps

Sub test()
    Set LrRng = Sheets("Data").Range("A2")
    For c = 1 To 10
        With LrRng.EntireColumn
            .Cells(.rows.count, 1).End(xlUp).Offset(1,0).value = NextNumber()
        End With
    Next c
end Sub

Function NextNumber() as Long

Dim i as Long


With Sheets("Data").Range("A:A")
    With Range(.Cells(2,1), .Cells(Rows.Count, 1).End(xlUp))
        I = WorksheetFunction.Min(.Cells)
        Do
            I = I + 1
        Loop Until IsError(Application.Match(I, .Cells, 0))
        .Offset(.Rows.Count,0).Cells(1,1).Value = I
    End With
End With

NextNumber = I

End Function

I see you start the for loop for the c variable but can't see it pointing to anything else. Am I wrong about that?

The controls "reg" are missing.

 

[mikerickson]

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"