Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!

You could try a VLOOKUP() formula but there are problems in deciding what to
do if the number exists and so does the next one after it. It might look
something like this (placed into A2 with 0 in A1)
=IF(ISNA(VLOOKUP(A1,Sheet2!B$1:B$4,1,0)),A1+1,A1+2 )
but when you get to 9 it will jump to 10, but if 10 is already in the list
also, then you get a duplicate that you don't want. So the problem is
determining how far ahead to increment to get a unique value.

A user defined function (UDF) can overcome that. I've written one that
would work for you. To put the code into your workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the editor's menu and copy
and paste the code below into it and change the sheet name and the references
to column B to your sheet name and the column the list is in on the 'control'
list (on your sheet2). Close the VB editor.

Back where you're adding new numbers, put a formula like this into the
worksheet, this example would go into A2:
= NextUnusedNumber(A1)
and just fill it on down the sheet like you have your incrementing formula
now. Here's the code:

Public Function NextUnusedNumber(lastValue As Long) As Long
Dim searchRange As Range
Dim testValue As Long
Dim haveValue As Boolean
Dim foundMatch As Boolean
Dim anyCell As Object

'change sheet name and column letter
'to match the sheet with the 'control' list
'and the column that list is in
Set searchRange = Worksheets("Sheet2").Range("B1:" & _
Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Address)

testValue = lastValue
Do Until haveValue = True
testValue = testValue + 1
foundMatch = False
For Each anyCell In searchRange
If anyCell.Value = testValue Then
foundMatch = True
End If
Next
If foundMatch = False Then
haveValue = True
NextUnusedNumber = testValue
End If
Loop
End Function


"kyoshirou" wrote:

Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!

The whole coding i copy inside a new module?

put a formula like this into the worksheet, this example would go into A2:
= NextUnusedNumber(A1)and just fill it on down the sheet like you have your
incrementing formula now. [This part i do not understand, you need i have to
insert = NextUnusedNumber(A1) into which worksheet? Do you mean sheet1(MAIN)?






"JLatham" wrote:

You could try a VLOOKUP() formula but there are problems in deciding what to
do if the number exists and so does the next one after it. It might look
something like this (placed into A2 with 0 in A1)
=IF(ISNA(VLOOKUP(A1,Sheet2!B$1:B$4,1,0)),A1+1,A1+2 )
but when you get to 9 it will jump to 10, but if 10 is already in the list
also, then you get a duplicate that you don't want. So the problem is
determining how far ahead to increment to get a unique value.

A user defined function (UDF) can overcome that. I've written one that
would work for you. To put the code into your workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the editor's menu and copy
and paste the code below into it and change the sheet name and the references
to column B to your sheet name and the column the list is in on the 'control'
list (on your sheet2). Close the VB editor.

Back where you're adding new numbers, put a formula like this into the
worksheet, this example would go into A2:
= NextUnusedNumber(A1)
and just fill it on down the sheet like you have your incrementing formula
now. Here's the code:

Public Function NextUnusedNumber(lastValue As Long) As Long
Dim searchRange As Range
Dim testValue As Long
Dim haveValue As Boolean
Dim foundMatch As Boolean
Dim anyCell As Object

'change sheet name and column letter
'to match the sheet with the 'control' list
'and the column that list is in
Set searchRange = Worksheets("Sheet2").Range("B1:" & _
Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Address)

testValue = lastValue
Do Until haveValue = True
testValue = testValue + 1
foundMatch = False
For Each anyCell In searchRange
If anyCell.Value = testValue Then
foundMatch = True
End If
Next
If foundMatch = False Then
haveValue = True
NextUnusedNumber = testValue
End If
Loop
End Function


"kyoshirou" wrote:

Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!

It does not have to go into a new module, if you already have another regular
module, you may paste it into that module.

Yes, the =NextUnusedNumber(A1) would go into cell A2 on the main sheet. If
0 is in A1 on that sheet, then it will return a 1.

"kyoshirou" wrote:

The whole coding i copy inside a new module?

put a formula like this into the worksheet, this example would go into A2:
= NextUnusedNumber(A1)and just fill it on down the sheet like you have your
incrementing formula now. [This part i do not understand, you need i have to
insert = NextUnusedNumber(A1) into which worksheet? Do you mean sheet1(MAIN)?






"JLatham" wrote:

You could try a VLOOKUP() formula but there are problems in deciding what to
do if the number exists and so does the next one after it. It might look
something like this (placed into A2 with 0 in A1)
=IF(ISNA(VLOOKUP(A1,Sheet2!B$1:B$4,1,0)),A1+1,A1+2 )
but when you get to 9 it will jump to 10, but if 10 is already in the list
also, then you get a duplicate that you don't want. So the problem is
determining how far ahead to increment to get a unique value.

A user defined function (UDF) can overcome that. I've written one that
would work for you. To put the code into your workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the editor's menu and copy
and paste the code below into it and change the sheet name and the references
to column B to your sheet name and the column the list is in on the 'control'
list (on your sheet2). Close the VB editor.

Back where you're adding new numbers, put a formula like this into the
worksheet, this example would go into A2:
= NextUnusedNumber(A1)
and just fill it on down the sheet like you have your incrementing formula
now. Here's the code:

Public Function NextUnusedNumber(lastValue As Long) As Long
Dim searchRange As Range
Dim testValue As Long
Dim haveValue As Boolean
Dim foundMatch As Boolean
Dim anyCell As Object

'change sheet name and column letter
'to match the sheet with the 'control' list
'and the column that list is in
Set searchRange = Worksheets("Sheet2").Range("B1:" & _
Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Address)

testValue = lastValue
Do Until haveValue = True
testValue = testValue + 1
foundMatch = False
For Each anyCell In searchRange
If anyCell.Value = testValue Then
foundMatch = True
End If
Next
If foundMatch = False Then
haveValue = True
NextUnusedNumber = testValue
End If
Loop
End Function


"kyoshirou" wrote:

Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!

Isee.
But my 0001 is by column A B C D.

it goes like this from increasing values:
A B C D
0 0 0 1
0 0 0 2
0 0 0 3

Erm.. any more helps?

"JLatham" wrote:

It does not have to go into a new module, if you already have another regular
module, you may paste it into that module.

Yes, the =NextUnusedNumber(A1) would go into cell A2 on the main sheet. If
0 is in A1 on that sheet, then it will return a 1.

"kyoshirou" wrote:

The whole coding i copy inside a new module?

put a formula like this into the worksheet, this example would go into A2:
= NextUnusedNumber(A1)and just fill it on down the sheet like you have your
incrementing formula now. [This part i do not understand, you need i have to
insert = NextUnusedNumber(A1) into which worksheet? Do you mean sheet1(MAIN)?






"JLatham" wrote:

You could try a VLOOKUP() formula but there are problems in deciding what to
do if the number exists and so does the next one after it. It might look
something like this (placed into A2 with 0 in A1)
=IF(ISNA(VLOOKUP(A1,Sheet2!B$1:B$4,1,0)),A1+1,A1+2 )
but when you get to 9 it will jump to 10, but if 10 is already in the list
also, then you get a duplicate that you don't want. So the problem is
determining how far ahead to increment to get a unique value.

A user defined function (UDF) can overcome that. I've written one that
would work for you. To put the code into your workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the editor's menu and copy
and paste the code below into it and change the sheet name and the references
to column B to your sheet name and the column the list is in on the 'control'
list (on your sheet2). Close the VB editor.

Back where you're adding new numbers, put a formula like this into the
worksheet, this example would go into A2:
= NextUnusedNumber(A1)
and just fill it on down the sheet like you have your incrementing formula
now. Here's the code:

Public Function NextUnusedNumber(lastValue As Long) As Long
Dim searchRange As Range
Dim testValue As Long
Dim haveValue As Boolean
Dim foundMatch As Boolean
Dim anyCell As Object

'change sheet name and column letter
'to match the sheet with the 'control' list
'and the column that list is in
Set searchRange = Worksheets("Sheet2").Range("B1:" & _
Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Address)

testValue = lastValue
Do Until haveValue = True
testValue = testValue + 1
foundMatch = False
For Each anyCell In searchRange
If anyCell.Value = testValue Then
foundMatch = True
End If
Next
If foundMatch = False Then
haveValue = True
NextUnusedNumber = testValue
End If
Loop
End Function


"kyoshirou" wrote:

Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!

What happens for 9 to 10 and 99 to 100? Does it look like this then?
A B C D
0 0 0 9
0 0 1 0
....
....
0 0 9 0
0 1 0 0
0 1 0 1

If so, it would complicate things quite a bit, and we might look at setting
up a helper column to use with a formula like this:
=(A1*1000)+(B1*100)+(C1*10)+D1
we could use that column to find unused values and the UDF could split the
individual digits across columns A:D more quickly.

"kyoshirou" wrote:

Isee.
But my 0001 is by column A B C D.

it goes like this from increasing values:
A B C D
0 0 0 1
0 0 0 2
0 0 0 3

Erm.. any more helps?

"JLatham" wrote:

It does not have to go into a new module, if you already have another regular
module, you may paste it into that module.

Yes, the =NextUnusedNumber(A1) would go into cell A2 on the main sheet. If
0 is in A1 on that sheet, then it will return a 1.

"kyoshirou" wrote:

The whole coding i copy inside a new module?

put a formula like this into the worksheet, this example would go into A2:
= NextUnusedNumber(A1)and just fill it on down the sheet like you have your
incrementing formula now. [This part i do not understand, you need i have to
insert = NextUnusedNumber(A1) into which worksheet? Do you mean sheet1(MAIN)?






"JLatham" wrote:

You could try a VLOOKUP() formula but there are problems in deciding what to
do if the number exists and so does the next one after it. It might look
something like this (placed into A2 with 0 in A1)
=IF(ISNA(VLOOKUP(A1,Sheet2!B$1:B$4,1,0)),A1+1,A1+2 )
but when you get to 9 it will jump to 10, but if 10 is already in the list
also, then you get a duplicate that you don't want. So the problem is
determining how far ahead to increment to get a unique value.

A user defined function (UDF) can overcome that. I've written one that
would work for you. To put the code into your workbook, press [Alt]+[F11] to
open the VB Editor, choose Insert | Module from the editor's menu and copy
and paste the code below into it and change the sheet name and the references
to column B to your sheet name and the column the list is in on the 'control'
list (on your sheet2). Close the VB editor.

Back where you're adding new numbers, put a formula like this into the
worksheet, this example would go into A2:
= NextUnusedNumber(A1)
and just fill it on down the sheet like you have your incrementing formula
now. Here's the code:

Public Function NextUnusedNumber(lastValue As Long) As Long
Dim searchRange As Range
Dim testValue As Long
Dim haveValue As Boolean
Dim foundMatch As Boolean
Dim anyCell As Object

'change sheet name and column letter
'to match the sheet with the 'control' list
'and the column that list is in
Set searchRange = Worksheets("Sheet2").Range("B1:" & _
Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Address)

testValue = lastValue
Do Until haveValue = True
testValue = testValue + 1
foundMatch = False
For Each anyCell In searchRange
If anyCell.Value = testValue Then
foundMatch = True
End If
Next
If foundMatch = False Then
haveValue = True
NextUnusedNumber = testValue
End If
Loop
End Function


"kyoshirou" wrote:

Hi All,
Inside my sheet2 got a column called ID.
Whatever dataID entered in sheet1 will be transfer to sheet2 column ID.

Previous i have old records inside sheet2 already. For example, 0009 is
already inside sheet2 column called ID.

Now,the ID in sheet1 is created by auto-increaseing value of 1. And will
start all over again. For example start from 0000. It will become 0001 follow
by 0002.
When the 9th records has to be created, it will produce 0009. But 0009 is
already inside sheet2 long ago.

How can i prevent that, 0009 from created? Any helps? Thanks!