Autonumbering in cell based on last row in column

[abdo meghari]

Hello,
I would autonumbering in E13 cell after INVOICE NO: when I sum the whole column C in last row
should show BSJ & QTY total and "-" and increment number after Hyphen .
I would increase numbering when click button and decreasing number when click another button
the buttons are not Active X
the data before

فاتورة 174305.xlsm
	A	B	C	D	E
13	PR_00001				INVOIECE NO :
14
15
16
17
18
19
20
21
22
23	ITEM	BRAND	QTY	PRICE	TOTAL
24	1	MOTORMECH 150A L KOR	2.00	1,000.00	2,000.00
25	2	NOVA 200A L KOR	2.00	1,500.00	3,000.00
26	3	VEGA 55A L KOR	1.00	400.00	400.00
27	4	XPRO 70A L KOR	4.00	540.00	2,160.00
28	5	XPRO 70A R KOR	4.00	540.00	2,160.00
29	6	BATTREY 30A CHI	1.00	275.00	275.00
30	7	BOHANO 55A L JAP	1.00	370.00	370.00
31	8	KM 215/65R16 TA21 KOR	5.00	435.00	2,175.00
32	9	KM 195/65R15 TA21 KOR	4.00	350.00	1,400.00
33	10	XPRO 150A L KOR	2.00	1,000.00	2,000.00
34	11	XPRO 100A L KOR	1.00	625.00	625.00
35	12	DUNLOP 285/60R18 AT25 JAP	1.00	1,110.00	1,110.00
36	13	APPLLO 265/65R17 INDIA	4.00	595.00	2,380.00
37	14	BS 1400R20 R180 JAP	2.00	4,500.00	9,000.00
38	15	XPRO 200A L KOR	4.00	1,285.00	5,140.00
39	16	XPRO 150A L KOR	4.00	1,000.00	4,000.00
40	17	XPRO 100A R KOR	10.00	610.00	6,100.00
41	18	XPRO 100A L KOR	10.00	620.00	6,200.00
42	19	XPRO 70A R KOR	15.00	510.00	7,650.00
43	20	XPRO 70A L KOR	10.00	515.00	5,150.00
44	21	XPRO 60A L KOR	15.00	425.00	6,375.00
45	22	XPRO 55A L KOR	10.00	390.00	3,900.00
46	23	YOKOHAMA 7A MALAYSIA	1.00	265.00	265.00
47	24	XPRO 80A L KPOR	1.00	570.00	570.00
48	25	HANKOOK 215/60R17 KOR	4.00	525.00	2,100.00
49	26	335/80R20 CHI	4.00	3,125.00	12,500.00
50	27	XPRO 70A R KOR	5.00	540.00	2,700.00
51	28	ASIMCO 70A R KOR	9.00	540.00	4,860.00
52	29	FORTEN 235/60R18 XL THI	4.00	445.00	1,780.00
53	30	TOP TRUST 6.00-16 8PR F2-1 TT CHI	2.00	365.00	730.00
54	31	PROLITE 74A R KOR	1.00	565.00	565.00
55	32	HANKOOK 235/60R17 K406 KOR	5.00	540.00	2,700.00
56	33	KM 155R13C KC55 KOR	4.00	415.00	1,660.00
57	34	KM 195R15C KC53 VIT	2.00	440.00	880.00
58	35	CRYSTAL 150A KOR	10.00	1,000.00	10,000.00
59	36	XPRO 150A L KOR	2.00	1,000.00	2,000.00
60	37	XPRO 70A L KOR	2.00	505.00	1,010.00
61	38	VEGA 70A L KOR	10.00	505.00	5,050.00
62	39	DUNLOP 285/60R18 AT25 JAP	5.00	1,125.00	5,625.00
63	40	PRINX 235/70R16 HH2 THI	4.00	475.00	1,900.00
64	41	BS 265/65R17 D693 THI	4.00	895.00	3,580.00
65	42	HANKOOK 215/70R16C KOR	4.00	490.00	1,960.00
66	43	XPRO 70A R KOR	2.00	515.00	1,030.00
67	44	PROLITE 44A L KOR	1.00	365.00	365.00
68	45	XPRO 74A L KOR	1.00	515.00	515.00
69	46	XPRO 70A L KOR	1.00	505.00	505.00
70	47	XPRO 90A L KOR	1.00	600.00	600.00
71	48	YOKOHAMA 205/65R16 ES32 JAP	4.00	455.00	1,820.00
72	49	DUNLOP 265/70R18 AT23 JAP	4.00	1,100.00	4,400.00
73	50	LASSA 4*4 235/70R16 A/T2 TR	4.00	575.00	2,300.00
74	51	LASSA 235/50R19 H/P2 TR	4.00	560.00	2,240.00
75	52	DUNLOP 285/65R17 JAP	2.00	950.00	1,900.00
76	53	VEGA 70A R KOR	5.00	500.00	2,500.00
77	54	XPRO 90A L KOR	9.00	605.00	5,445.00
78	55	XPRO 80A L KPOR	1.00	570.00	570.00
79	56	KM 285/75R16 MT51 VIT	8.00	835.00	6,680.00
80	57	XPRO 60A L KOR	2.00	425.00	850.00
81	58	KM 245/70R17 AT52 VIT	4.00	645.00	2,580.00
82	59	TIRES 245/50R19 DFG CHI	4.00	1,000.00	4,000.00
83	TOTAL		252.00		174,305.00
SH1

Cell Formulas
Range	Formula
C83,E83	C83	=SUM(C24:C82)
E24:E82	E24	=C24*D24

result

فاتورة 174305.xlsm
	A	B	C	D	E
12	PR_00001				فاتورة رقم
13	PR_00001				INVOIECE NO : BSJ252-1
14
15
16
17
18
19
20
21
22
23	ITEM	BRAND	QTY	PRICE	TOTAL
24	1	MOTORMECH 150A L KOR	2.00	1,000.00	2,000.00
25	2	NOVA 200A L KOR	2.00	1,500.00	3,000.00
26	3	VEGA 55A L KOR	1.00	400.00	400.00
27	4	XPRO 70A L KOR	4.00	540.00	2,160.00
28	5	XPRO 70A R KOR	4.00	540.00	2,160.00
29	6	BATTREY 30A CHI	1.00	275.00	275.00
30	7	BOHANO 55A L JAP	1.00	370.00	370.00
31	8	KM 215/65R16 TA21 KOR	5.00	435.00	2,175.00
32	9	KM 195/65R15 TA21 KOR	4.00	350.00	1,400.00
33	10	XPRO 150A L KOR	2.00	1,000.00	2,000.00
34	11	XPRO 100A L KOR	1.00	625.00	625.00
35	12	DUNLOP 285/60R18 AT25 JAP	1.00	1,110.00	1,110.00
36	13	APPLLO 265/65R17 INDIA	4.00	595.00	2,380.00
37	14	BS 1400R20 R180 JAP	2.00	4,500.00	9,000.00
38	15	XPRO 200A L KOR	4.00	1,285.00	5,140.00
39	16	XPRO 150A L KOR	4.00	1,000.00	4,000.00
40	17	XPRO 100A R KOR	10.00	610.00	6,100.00
41	18	XPRO 100A L KOR	10.00	620.00	6,200.00
42	19	XPRO 70A R KOR	15.00	510.00	7,650.00
43	20	XPRO 70A L KOR	10.00	515.00	5,150.00
44	21	XPRO 60A L KOR	15.00	425.00	6,375.00
45	22	XPRO 55A L KOR	10.00	390.00	3,900.00
46	23	YOKOHAMA 7A MALAYSIA	1.00	265.00	265.00
47	24	XPRO 80A L KPOR	1.00	570.00	570.00
48	25	HANKOOK 215/60R17 KOR	4.00	525.00	2,100.00
49	26	335/80R20 CHI	4.00	3,125.00	12,500.00
50	27	XPRO 70A R KOR	5.00	540.00	2,700.00
51	28	ASIMCO 70A R KOR	9.00	540.00	4,860.00
52	29	FORTEN 235/60R18 XL THI	4.00	445.00	1,780.00
53	30	TOP TRUST 6.00-16 8PR F2-1 TT CHI	2.00	365.00	730.00
54	31	PROLITE 74A R KOR	1.00	565.00	565.00
55	32	HANKOOK 235/60R17 K406 KOR	5.00	540.00	2,700.00
56	33	KM 155R13C KC55 KOR	4.00	415.00	1,660.00
57	34	KM 195R15C KC53 VIT	2.00	440.00	880.00
58	35	CRYSTAL 150A KOR	10.00	1,000.00	10,000.00
59	36	XPRO 150A L KOR	2.00	1,000.00	2,000.00
60	37	XPRO 70A L KOR	2.00	505.00	1,010.00
61	38	VEGA 70A L KOR	10.00	505.00	5,050.00
62	39	DUNLOP 285/60R18 AT25 JAP	5.00	1,125.00	5,625.00
63	40	PRINX 235/70R16 HH2 THI	4.00	475.00	1,900.00
64	41	BS 265/65R17 D693 THI	4.00	895.00	3,580.00
65	42	HANKOOK 215/70R16C KOR	4.00	490.00	1,960.00
66	43	XPRO 70A R KOR	2.00	515.00	1,030.00
67	44	PROLITE 44A L KOR	1.00	365.00	365.00
68	45	XPRO 74A L KOR	1.00	515.00	515.00
69	46	XPRO 70A L KOR	1.00	505.00	505.00
70	47	XPRO 90A L KOR	1.00	600.00	600.00
71	48	YOKOHAMA 205/65R16 ES32 JAP	4.00	455.00	1,820.00
72	49	DUNLOP 265/70R18 AT23 JAP	4.00	1,100.00	4,400.00
73	50	LASSA 4*4 235/70R16 A/T2 TR	4.00	575.00	2,300.00
74	51	LASSA 235/50R19 H/P2 TR	4.00	560.00	2,240.00
75	52	DUNLOP 285/65R17 JAP	2.00	950.00	1,900.00
76	53	VEGA 70A R KOR	5.00	500.00	2,500.00
77	54	XPRO 90A L KOR	9.00	605.00	5,445.00
78	55	XPRO 80A L KPOR	1.00	570.00	570.00
79	56	KM 285/75R16 MT51 VIT	8.00	835.00	6,680.00
80	57	XPRO 60A L KOR	2.00	425.00	850.00
81	58	KM 245/70R17 AT52 VIT	4.00	645.00	2,580.00
82	59	TIRES 245/50R19 DFG CHI	4.00	1,000.00	4,000.00
83	TOTAL		252.00		174,305.00
SH1

Cell Formulas
Range	Formula
C83,E83	C83	=SUM(C24:C82)
E24:E82	E24	=C24*D24

the first time will be BSJ252-1 when click button to increase number , but if I click another button to decrease then will not decrease
next time when increase will be BSJ252-2 and if I decrease then will be BSJ252-1.
thanks in advanced.

 

[DanteAmor]

Create 2 buttons. One for increase other for decrease

For the increase button you put the following code:

Sub increaseNum()
  Call in_de("+")
End Sub

For the decrease button you put the following code:

Sub decreaseNum()
  Call in_de("-")
End Sub

In the same module put the following code:

Sub in_de(op As String)
  Dim c As Range
  Dim lr As Long
  Dim tot As Double
  Dim s1 As String, s2 As String
  
  Const px1 = "INVOICE NO : "
  Const px2 = "BSJ"
  Set c = Range("E13")
  
  lr = Range("A" & Rows.Count).End(3).Row
  If Range("A" & lr).Value = "TOTAL" Then
    tot = Range("C" & lr).Value
    If c.Value = "" Or Left(c.Value, Len(px1)) <> px1 Or InStr(1, c.Value, "-") = 0 Then
      c.Value = px1 & px2 & tot & "-1"
    Else
      s1 = Split(c.Value, "-")(0)
      s2 = Val(Split(c.Value, "-")(1))
      If op = "+" Then
        s2 = s2 + 1
      Else
        If s2 - 1 > 0 Then s2 = s2 - 1
      End If
      c.Value = px1 & px2 & tot & "-" & s2
    End If
  Else
    MsgBox "TOTAL does not exists"
  End If
End Sub

Try and comment.

 

[abdo meghari]

Hi Dante again ,
this is excellent !
many thanks