Number Items Dynamically with conditions

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
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.
Code:
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
This brute force approach will work

Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

I am using a for loop with this line.
Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Code:
[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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
Perhaps
Code:
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
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
Perhaps
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top