# Number Items Dynamically with conditions

#### kelly mort

##### Well-known Member
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``````

### Excel Facts

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

#### mikerickson

##### MrExcel MVP
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
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

#### mikerickson

##### MrExcel MVP
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

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

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
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
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
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"

Replies
0
Views
57
Replies
0
Views
171
Replies
0
Views
274
Replies
2
Views
60
Replies
11
Views
137