Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Code numbering invoice

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code numbering invoice

    hi, expert
    i have userform connect with sheet i would show the next number in textbox3
    the textbox3 connected with range in my sheet f9 i know this code range("f9").value=range("f9").value+1
    but my numbering is coplicated contains letters and numbers and symbols here my numbring is bsjd-001
    i hope help me

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    624
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    Hello. If your numbering always ends with the number you want to add to, then this will do it. Replace the bsjd- with another text if necessary.
    Code:
    Dim Prefixtext As String
    Prefixtext = "bsjd-"
    Range("f9").Value = Prefixtext + Format(Mid(Range("F9").Value, Len(Prefixtext) + 1) + 1, "000")
    This will always add 1 to the number at the end and keep it as 3 digits (i.e. 002,003 etc) (and when it gets to 999 it will carry on with 1000, 1001, etc.. If there may be text after the number, then a more complicated solution is needed.
    Last edited by jmacleary; Sep 9th, 2019 at 06:42 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by jmacleary View Post
    Hello. If your numbering always ends with the number you want to add to, then this will do it. Replace the bsjd- with another text if necessary.
    Code:
    Dim Prefixtext As String
    Prefixtext = "bsjd-"
    Range("f9").Value = Prefixtext + Format(Mid(Range("F9").Value, Len(Prefixtext) + 1) + 1, "000")
    This will always add 1 to the number at the end and keep it as 3 digits (i.e. 002,003 etc) (and when it gets to 999 it will carry on with 1000, 1001, etc.. If there may be text after the number, then a more complicated solution is needed.


    thanks so much the code is worked but i would show the next number in textbox3 in userform directly when i run userform

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,032
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Code numbering invoice

    Hi,
    You use the Range.NumberFormat property to do what you want

    Code:
    With Range("B9")
            .NumberFormat = """bsjd-""000"
            .Value = .Value + 1
        End With
    Although you will see your text prefix in the cell, it's underlying value is just a number which you can increment in manner required

    Note: ensure when you first run the code that you only have a number (no prefix) in the cell. Code will add this for you.

    Dave
    Last edited by dmt32; Sep 9th, 2019 at 07:12 AM.

  5. #5
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by dmt32 View Post
    Hi,
    You use the Range.NumberFormat property to do what you want

    Code:
    With Range("B9")
            .NumberFormat = """bsjd-""000"
            .Value = .Value + 1
        End With
    Although you will see your text prefix in the cell, it's underlying value is just a number which you can increment in manner required

    Note: ensure when you first run the code that you only have a number (no prefix) in the cell. Code will add this for you.

    Dave

    thanks for your replying but your code give me error run time13
    the error line is: .Value = .Value + 1

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,032
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by abdelfattah View Post
    thanks for your replying but your code give me error run time13
    the error line is: .Value = .Value + 1
    Did you follow the note I added when first using the code?

    Note: ensure when you first run the code ensure that you only have a number (no prefix) in the cell. Code will add this for you.
    Dave
    Last edited by dmt32; Sep 9th, 2019 at 08:02 AM.

  7. #7
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by dmt32 View Post
    Did you follow the note I added when first using the code?



    Dave
    sorry i don't follow your note now the code works perfectly but there is one thing i would show the next number in texrbox3 in userform when i run userform directly

  8. #8
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    624
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    abdel. Where are you putting the code we have provided (either solution works, mine fills the f9 cell with the full text, Dave's with just the number? You would need to refresh the data in the userform itself once the field has been updated.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  9. #9
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,032
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by abdelfattah View Post
    sorry i don't follow your note now the code works perfectly but there is one thing i would show the next number in texrbox3 in userform when i run userform directly


    Something like following maybe:

    Code:
      With Range("B9")
            .NumberFormat = """bsjd-""000"
            .Value = .Value + 1
            Me.TextBox3.Text = .Text
        End With
    Dave

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code numbering invoice

    Quote Originally Posted by jmacleary View Post
    abdel. Where are you putting the code we have provided (either solution works, mine fills the f9 cell with the full text, Dave's with just the number? You would need to refresh the data in the userform itself once the field has been updated.
    i put the code in
    Code:
    Private Sub UserForm_Initialize()
    TextBox3.Text = Range("F9").Value
    
    end sub
    then show me the numbering invoice is 2 not
    bsjd-002
    is there any way to sole this problem

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •