Help with inserting next Text based Number

[Gregm66]

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...

 

[lrobbo314]

How will they run consecutively? Just BCPSC01-BCPSC99? What comes next? BCPSC100? Or do the letters change?

 

[Gregm66]

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.

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

 

[Gregm66]

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