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!

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
.......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

For the second part of your post ("du u mean ...") -- that is a possibility,
depending on whether what I'm about to offer will work for you or not. This
solution uses formulas in cells from the second row on down the sheet to
build up the entries. Try these and see if it works for you:

A B C D
1 0 0 0 0
2

In cell A2 put this formula:
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)

In cell B2 put this formula
=IF(AND(C1=9,D1=9),B1+1,B1)

In cell C2 put this formula
=IF(D1=9,C1+1,C1)

and in cell D2 put this formula:
=IF(D1=9,0,D1+1)

You can now fill this down the sheet and it should build sequential numbers
for you. You can even test to make sure by changing the numbers in row 1 and
watching what happens to the values below row 1.

If this will work for you, then we are finished. If this doesn't work for
some reason, then we may have to use a helper column type of solution.


"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

OOPS! Found an error in the previous formulas. Correct formulas for row 2
a

In A2 same as before
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)
- note that when you finally get to 9 9 9 9 then column A will go from 9 to
10.

In B2
=IF(AND(C1=9,D1=9),IF(B1+19,0,B1+1),B1)

In C2
=IF(D1=9,IF(C1+19,0,C1+1),C1)

and finally in D2 also same as before
=IF(D1=9,0,D1+1)

None of this compares anything against the values in Sheet2. If we still
need to do that, let me know also.

"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

it cant works like that.

is like i have add records functions inside sheet1. When added, records will
be store inside sheet2(Data).
sheet1, i entered "Apple", sheet2 called Data have a column called ABCD will
give an ID of 0001 to Apple. If entered another item orange or pen, it will
give 0002, 0003.

For example in existing database
A B C D
0 0 9 7 -- Apple
0 0 9 8 -- orange
0 0 9 9 -- pen

Then now i will start from 0000 despite having some old records inside. It
means:
A B C D
0 0 9 7
0 0 9 8
0 0 9 9
0 0 0 0 -- new starting value of 0000.

Therefore new data enter will be assigned ID of 0001. And so on adding by 1
after each data is added. Then when comes to 0097, this 0097 should not be
added inside since 0097 already exist. So it it possible to jump to 0098? But
0098 is exit. So jump to an unusedID of 0100.

Hope is not blue.


0 0 0 1 -- new Apple.



"JLatham" wrote:

OOPS! Found an error in the previous formulas. Correct formulas for row 2
a

In A2 same as before
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)
- note that when you finally get to 9 9 9 9 then column A will go from 9 to
10.

In B2
=IF(AND(C1=9,D1=9),IF(B1+19,0,B1+1),B1)

In C2
=IF(D1=9,IF(C1+19,0,C1+1),C1)

and finally in D2 also same as before
=IF(D1=9,0,D1+1)

None of this compares anything against the values in Sheet2. If we still
need to do that, let me know also.

"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

I'll look at all of this some more tonight. Too bad it wouldn't work with
just the formulas. I believe I will end up using a 'helper' column as I
mentioned before and we will work from that - along with some code similar to
the UDF code I provided earlier. Using a helper column will be much faster
than trying to combine all of the 0 0 0 1 ... 0 0 9 9 entries each time you
add a new item to your list.

"kyoshirou" wrote:

it cant works like that.

is like i have add records functions inside sheet1. When added, records will
be store inside sheet2(Data).
sheet1, i entered "Apple", sheet2 called Data have a column called ABCD will
give an ID of 0001 to Apple. If entered another item orange or pen, it will
give 0002, 0003.

For example in existing database
A B C D
0 0 9 7 -- Apple
0 0 9 8 -- orange
0 0 9 9 -- pen

Then now i will start from 0000 despite having some old records inside. It
means:
A B C D
0 0 9 7
0 0 9 8
0 0 9 9
0 0 0 0 -- new starting value of 0000.

Therefore new data enter will be assigned ID of 0001. And so on adding by 1
after each data is added. Then when comes to 0097, this 0097 should not be
added inside since 0097 already exist. So it it possible to jump to 0098? But
0098 is exit. So jump to an unusedID of 0100.

Hope is not blue.


0 0 0 1 -- new Apple.



"JLatham" wrote:

OOPS! Found an error in the previous formulas. Correct formulas for row 2
a

In A2 same as before
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)
- note that when you finally get to 9 9 9 9 then column A will go from 9 to
10.

In B2
=IF(AND(C1=9,D1=9),IF(B1+19,0,B1+1),B1)

In C2
=IF(D1=9,IF(C1+19,0,C1+1),C1)

and finally in D2 also same as before
=IF(D1=9,0,D1+1)

None of this compares anything against the values in Sheet2. If we still
need to do that, let me know also.

"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

Since this is very tailored code, I've simply built a working 'proof of
concept' workbook with it in it. I think you'll be able to adapt the code to
your real workbook. The code is documented as to what you would need to
change, and Sheet1 in it has 'rules' and instructions on how to use it along
with examples of data already entered in to it showing how it is keeping
track of used numbers and making new assignments by the rules as I understand
them.

The file should be usable by any version of Excel from 95 through 2007.
Click this link and choose SAVE to your hard drive to check it out:
http://www.jlathamsite.com/uploads/G...yItemEntry.xls

I hope this helps with your situation. My email address is in the workbook.

"kyoshirou" wrote:

it cant works like that.

is like i have add records functions inside sheet1. When added, records will
be store inside sheet2(Data).
sheet1, i entered "Apple", sheet2 called Data have a column called ABCD will
give an ID of 0001 to Apple. If entered another item orange or pen, it will
give 0002, 0003.

For example in existing database
A B C D
0 0 9 7 -- Apple
0 0 9 8 -- orange
0 0 9 9 -- pen

Then now i will start from 0000 despite having some old records inside. It
means:
A B C D
0 0 9 7
0 0 9 8
0 0 9 9
0 0 0 0 -- new starting value of 0000.

Therefore new data enter will be assigned ID of 0001. And so on adding by 1
after each data is added. Then when comes to 0097, this 0097 should not be
added inside since 0097 already exist. So it it possible to jump to 0098? But
0098 is exit. So jump to an unusedID of 0100.

Hope is not blue.


0 0 0 1 -- new Apple.



"JLatham" wrote:

OOPS! Found an error in the previous formulas. Correct formulas for row 2
a

In A2 same as before
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)
- note that when you finally get to 9 9 9 9 then column A will go from 9 to
10.

In B2
=IF(AND(C1=9,D1=9),IF(B1+19,0,B1+1),B1)

In C2
=IF(D1=9,IF(C1+19,0,C1+1),C1)

and finally in D2 also same as before
=IF(D1=9,0,D1+1)

None of this compares anything against the values in Sheet2. If we still
need to do that, let me know also.

"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!

RECAP and FINAL SOLUTION: After actually seeing the workbook and a bit of
stumbling, we finally came up with a function to achieve the goal of creating
unique numbers in the 'master list'. Essentially there was no list on the
main sheet - only on the data sheet and a unique number was requested at the
end of the data entry process. A helper column was not desired, so we worked
with actually converting individual digits in 4 cells in a row, storing them
all in an array and then determining what the next available sequence number
was. There is the possibility of the individual digits cells being empty or
containing non-numeric data, so error trapping and value testing code was
added to deal with those situations. The calling routine has already
determined the last used row in the group of digits columns and what the
apparent last used value in the list was and passes those to this function:

Private Function fncNextSequence(copyLastRow As Long, _
copyLastSequence As Long) As Long
'NOTE: 'Private' makes this usable only by other routines in this code module
'
' INPUT: uses module level values:
' lastrow (copied here as copyLastRow)
' lastsequence (copied here as copyLastSequence)
' OUTPUT:
' SUCCESSFUL:
' returns next available unused value
' OR IF UNSUCCESSFUL:
' returns 0 or 10000 depending on the problem.
'
' Coding basis:
' values we need are in worksheet "Data" and
' are in columns A, B, C and D
' entries start on row 2.
' copyLastRow points to last entry in the list
' copyLastRow is a copy of lastRow in other sections.
' copyLastSequence is a copy of the lastsequence
' as determined in other sections.
Const firstDataRow = 2
Dim allValues() As Integer
Dim baseCell As Range ' will point to first entry at A2
Dim rOffset As Long
Dim cOffset As Integer
Dim isUnusedValue As Boolean
'added in version 4
Dim Thousands As Integer
Dim Hundreds As Integer
Dim Tens As Integer
Dim Units As Integer

If copyLastRow < firstDataRow Then
'firstDataRow is set = 2 in this function
'so if lastRow (copyLastRow) is 0 or 1 then
'there is no work to be done, and the code will
'not function properly.
'Notify the user of this and return value of zero
MsgBox "There is No Data to work with at this time."
fncNextSequence = 0
End If
ReDim allValues(firstDataRow To copyLastRow)

Set baseCell = Worksheets("Data").Range("A" & firstDataRow)
For rOffset = 0 To copyLastRow - firstDataRow
If IsNumeric(baseCell.Offset(rOffset, 0)) Then
Thousands = baseCell.Offset(rOffset, 0)
Else
Thousands = 0
End If
If IsNumeric(baseCell.Offset(rOffset, 1)) Then
Hundreds = baseCell.Offset(rOffset, 1)
Else
Hundreds = 0
End If
If IsNumeric(baseCell.Offset(rOffset, 2)) Then
Tens = baseCell.Offset(rOffset, 2)
Else
Tens = 0
End If
If IsNumeric(baseCell.Offset(rOffset, 3)) Then
Units = baseCell.Offset(rOffset, 3)
Else
Units = 0
End If
allValues(rOffset + firstDataRow) = _
Thousands * 10 ^ 3 _
+ Hundreds * 10 ^ 2 _
+ Tens * 10 _
+ Units
Next
fncNextSequence = copyLastSequence
Do While isUnusedValue = False And fncNextSequence < 10000
fncNextSequence = fncNextSequence + 1
isUnusedValue = True
For rOffset = LBound(allValues) To UBound(allValues)
If fncNextSequence = allValues(rOffset) Then
isUnusedValue = False
Exit For
End If
Next
Loop
'when it exits here
'fncNextSequence will either contain
' 10000 meaning all available numbers have been used!
'or
' the next available value
End Function

"kyoshirou" wrote:

it cant works like that.

is like i have add records functions inside sheet1. When added, records will
be store inside sheet2(Data).
sheet1, i entered "Apple", sheet2 called Data have a column called ABCD will
give an ID of 0001 to Apple. If entered another item orange or pen, it will
give 0002, 0003.

For example in existing database
A B C D
0 0 9 7 -- Apple
0 0 9 8 -- orange
0 0 9 9 -- pen

Then now i will start from 0000 despite having some old records inside. It
means:
A B C D
0 0 9 7
0 0 9 8
0 0 9 9
0 0 0 0 -- new starting value of 0000.

Therefore new data enter will be assigned ID of 0001. And so on adding by 1
after each data is added. Then when comes to 0097, this 0097 should not be
added inside since 0097 already exist. So it it possible to jump to 0098? But
0098 is exit. So jump to an unusedID of 0100.

Hope is not blue.


0 0 0 1 -- new Apple.



"JLatham" wrote:

OOPS! Found an error in the previous formulas. Correct formulas for row 2
a

In A2 same as before
=IF(AND(B1=9,C1=9,D1=9),A1+1,A1)
- note that when you finally get to 9 9 9 9 then column A will go from 9 to
10.

In B2
=IF(AND(C1=9,D1=9),IF(B1+19,0,B1+1),B1)

In C2
=IF(D1=9,IF(C1+19,0,C1+1),C1)

and finally in D2 also same as before
=IF(D1=9,0,D1+1)

None of this compares anything against the values in Sheet2. If we still
need to do that, let me know also.

"kyoshirou" wrote:

No.. it should be
A B C D
0 0 9 0
0 0 9 1
0 0 9 2
to....
0 0 9 9
0 1 0 0
0 1 0 1
......
do u mean that i will need to create new column (test), to store column ABCD
values into a string. Then we can have a forumula to validate the string
values?
Then earlier on those coding u wrote, will it plays a part?

"JLatham" wrote:

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!