Help with inserting next Text based Number
Results 1 to 4 of 4

Thread: Help with inserting next Text based Number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2016
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with inserting next Text based Number

    Hi Everyone,

    I wish to Auto insert a text based number into a worksheet using vba...
    The text based numbers appear like this "BCPSC01", i have tried a few different methods but no success so far, i know this is possible to do.
    the numbers will continue to run consecutively.

    Thankyou in advance...

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with inserting next Text based Number

    How will they run consecutively? Just BCPSC01-BCPSC99? What comes next? BCPSC100? Or do the letters change?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    Board Regular
    Join Date
    Jan 2016
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with inserting next Text based Number

    Quote Originally Posted by lrobbo314 View Post
    How will they run consecutively? Just BCPSC01-BCPSC99? What comes next? BCPSC100? Or do the letters change?
    The numbers run as follows,
    BCPSC01
    BCPSC02
    BCPSC03

    and so on, but only to go into column "A" if a name has been selected in column "B", i have the following code that inserts a number only IE: 1 next cell 2 and so on it wont insert the BCPSC part.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Range("B:B"), Target) Is Nothing Then
            For Each cell In Intersect(Range("B:B"), Target).Cells
                If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
                    Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1
                End If
            Next
        End If
    End Sub

  4. #4
    Board Regular
    Join Date
    Jan 2016
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with inserting next Text based Number

    Thanks to everyone who looked at my post, and or replied.

    I have solved my problem by creating a user form to add all my data and to create a membership number consisting of text and numbers...

    Thankyou all

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
  •