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!