generate the next sequential number

[kelvin_9]

Hello All,
Previously, I use this code to start record my customer inquiry daily, and now I want to amend it and easier for analysis monthly records if my superior needs. However I get stuck on sequential number generation.

Sub Macro1()

Range("A1000").End(xlUp).Offset(1, 0).Select
Selection.Value = Selection.Offset(-1, 0).Value + 1

End Sub

I created a calendar in VBA at first, select the inquiry date when someone inquired, and secondly, I wonder can it be generate the next new sequential number automatic like below table.

Book1
	A		C
1	BASIC INFORMATION
2	DATE (MM-DD-YYYY)		CASE #
3	Apr-05-2020		1
4	Apr-05-2020		2
5	Apr-06-2020		1
6	Apr-06-2020		2
7	Apr-06-2020		3
8	Apr-06-2020		4
9	Apr-06-2020		5
10	Apr-06-2020		6
11	Apr-07-2020		1
12	Apr-07-2020		2
13	Apr-07-2020		3
14	Apr-07-2020		4
15	Apr-07-2020		5
16	Apr-07-2020		6
17	Apr-07-2020		7
18	Apr-07-2020		8
19	Apr-08-2020		1
20	Apr-08-2020		2
21	Apr-08-2020		3
22	Apr-08-2020		4
DATABASE

remarks:
case# in column C should be using 001, 002, 003, 004...... instead of single numbers 1, 2, 3, 4
(I curious why the outcome was single. I'm sorry)

Thanks All for the great guidance.
kelvin

 

[Joe4]

You actually do not need VBA for this. It can be done with a simple formula.
Put this in cell C3 and copy down:
=IF(A3=A2,C2+1,1)

Then, if you want it to display in "001" format, simply add a Custom Format to column C of 000.

 

[kelvin_9]

Thanks Joe4. It works perfectly!
Can I still have this "case # text box" display the next new sequential number? like year+month+case# which is "20""04"-"001", "20""04"-"002"....

Thanks & Regards,
kelvin

 

[Joe4]

Does this formula give you what you want?
=TEXT(A3,"yymm") & "-" & TEXT(C3,"000")

 

[kelvin_9]

Almost there. this come with "Apr-05-2020-001"

Expected format: 20APR-001, 20APR-002.........20MAY-001, 20MAY-002, 20MAY-003..... showing in "case # text box"
Expected goal: the case number is generate autonomic when one case is completed and saved. Assuming that, I'm filing a new case so I will get 001 first, after that, i will get 002 and so on.

Apology for my poor presentation.

Thanks & Regards,
kelvin

 

[Joe4]

Almost there. this come with "Apr-05-2020-001"

Expected format: 20APR-001, 20APR-002.........20MAY-001, 20MAY-002, 20MAY-003..... showing in "case # text box"

OK, I thought you said you wanted the following format:

Can I still have this "case # text box" display the next new sequential number? like year+month+case# which is "20""04"-"001", "20""04"-"002"....

If you want it to look like "20APR-001", then use:
=UPPER(TEXT(A3,"yymmm")) & "-" & TEXT(C3,"000")

 

[kelvin_9]

OK, I thought you said you wanted the following format:

If you want it to look like "20APR-001", then use:
=UPPER(TEXT(A3,"yymmm")) & "-" & TEXT(C3,"000")

I want "year+month-case#" ?

Book1
	A		C	D	E
1	BASIC INFORMATION
2	DATE (MM-DD-YYYY)		CASE #
3	Apr-05-2020		001	APR-05-2020-001	20APR-001
4	Apr-05-2020		002	APR-05-2020-002	20APR-002
5	Apr-06-2020		001	APR-06-2020-001	20APR-003
6	Apr-06-2020		002	APR-06-2020-002	20APR-004
7	Apr-06-2020		003	APR-06-2020-003	20APR-005
8	Apr-06-2020		004	APR-06-2020-004	20APR-006
9	Apr-06-2020		005	APR-06-2020-005	20APR-007
10	Apr-06-2020		006	APR-06-2020-006	20APR-008
11	Apr-07-2020		001	APR-07-2020-001	20APR-009
12	Apr-07-2020		002	APR-07-2020-002	20APR-010
13	Apr-07-2020		003	APR-07-2020-003	20APR-011
14	Apr-07-2020		004	APR-07-2020-004	20APR-012
15	Apr-07-2020		005	APR-07-2020-005	20APR-013
16	Apr-07-2020		006	APR-07-2020-006	20APR-014
17	Apr-07-2020		007	APR-07-2020-007	20APR-015
18	Apr-07-2020		008	APR-07-2020-008	20APR-016
19	Apr-08-2020		001	APR-08-2020-001	20APR-017
DATABASE

Cell Formulas
Range	Formula
C3:C19	C3	=IF(A3=A2,C2+1,1)
D3:D19	D3	=UPPER(TEXT(A3,"yymmm")) & "-" & TEXT(C3,"000")

In addition, where should i put this formula and then showing in "case # text box" / how can i generate the next new number automatic?

Thanks & Regards,
kelvin

 

[Joe4]

I want "year+month-case#"

I am sorry, you are not being very clear here. That is not very helpful, as that does not show me the format you want for each piece (month can be shown in at least a half-dozen different ways).
Please show me an example of EXACTLY what you want it to look like.

how can i generate the next new number automatic

You really cannot do that ahead of time, because it depends on the date being entered, right? Until we know the data, we don't know the next number.
The formulas are dynamic, so as soon as you enter a new value in column A, the formulas in the other cells should calculate what you need.

 

[kelvin_9]

Sorry Joe, sorry for my poor english & presentation.

Appreciate if I could start it all over again and try to be simple. Part of the form including:
Color Tan: case#, start to filing a guest inquiry, first select the date, second generate a next new case# automatic
Color Aqua: reference#, for guest & admin usage, easy to locate/search by providing this reference#
(I.E. 20 means year, 04 or 05 means month, 000 means case#)

When I start to record an inquiry, I open-up the vba form, I select the date and I wish the case# will be populated a next one since last saved.

Book1
	A		C	D
1	BASIC INFORMATION
2	DATE (MM-DD-YYYY)		CASE #	REFERENCE #
3	Apr-05-2020		001	2004-001
4	Apr-05-2020		002	2004-002
5	Apr-06-2020		003	2004-003
6	Apr-06-2020		004	2004-004
7	Apr-06-2020		005	2004-005
8	May-01-2020		001	2005-001
9	May-01-2020		002	2005-002
10	May-02-2020		003	2005-003
11	May-03-2020		004	2005-004
12	May-03-2020		005	2005-005
13	May-03-2020		006	2005-006
14	May-04-2020		007	2005-007
DATABASE

Thanks & Regards,
kelvin

 

[Joe4]

My first two replies (posts 2 and 4 above), give you the exact formulas you need for column C and D in your last post.

When I start to record an inquiry, I open-up the vba form, I select the date and I wish the case# will be populated a next one since last saved.

Does this form automatically choose the current date, or does the user supply the date?
Are you saying that you want the next sequence number to appear on this form right away (once date is decided)?

If the user supplies the date, it is going to need to wait for them to populate the date, and then look up on the table what the next value for that date should be.

If they are allowed to pick there own dates, what happens if they choose a date older than the last record in the table in Excel?
After writing it to Excel, do you have VBA re-sort that list?

Please post all the VBA code you have behind this form.