Create a Macro with button to generate the next sequential number in an Alphanumeric string

[mangotti]

I looking for some assistance in creating a macro that when a button is clicked, the macro sequentially increases an alphanumeric string, example of the string is as follows and the number should increase by 1 after the macro is run.

CA-123456

When macro is run, next alphanumeric will be CA-123457

Thanks!

 

[Mike LH]

Hi,

Where? In the same cell? In another cell?

 

[mangotti]

Hi,

Where? In the same cell? In another cell?

Apologies for not clarifying, it would be the same cell where the update would occur.

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG27Apr46
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]If[/COLOR] InStr(Selection, "-") > 0 [COLOR="Navy"]Then[/COLOR]
    Sp = Split(Selection, "-")
    Selection = Sp(0) & "-" & Sp(1) + 1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mangotti]

Try:-

[COLOR=Navy]Sub[/COLOR] MG27Apr46
[COLOR=Navy]Dim[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]If[/COLOR] InStr(Selection, "-") > 0 [COLOR=Navy]Then[/COLOR]
    Sp = Split(Selection, "-")
    Selection = Sp(0) & "-" & Sp(1) + 1
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks Mick.
I have tried this but receive an error to debug this line

If InStr(Selection, "B4") > 0 Then

Below is my code. I assume the "-" is the cell reference and my number to increment is in cell B4

Sub MG27Apr46()
Dim Sp As Variant
If InStr(Selection, "B4") > 0 Then
    Sp = Split(Selection, "B4")
    Selection = Sp(0) & "B4" & Sp(1) + 1
End If
End Sub

 

[Rick Rothstein]

Assuming cell B4 is the cell with the number...

Sub AddOneToCellB4()
  [b4] = Application.Substitute([b4], Right([b4], 6), Right([b4], 6) + 1)
End Sub

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG27Apr51
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] Range("B4")
Sp = Split(.Value, "-")
    .Value = Sp(0) & "-" & Sp(1) + 1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mangotti]

Try:-

[COLOR=Navy]Sub[/COLOR] MG27Apr51
[COLOR=Navy]Dim[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]With[/COLOR] Range("B4")
Sp = Split(.Value, "-")
    .Value = Sp(0) & "-" & Sp(1) + 1
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Mick,

Now I receive a runtime error "9": "Subset is out of range". It debugs this line:

Value = Sp(0) & "-" & Sp(1) + 1

 

[mangotti]

This only works for fields containing numeric only. My field is alphanumeric.

Assuming cell B4 is the cell with the number...

Sub AddOneToCellB4()
  [b4] = Application.Substitute([b4], Right([b4], 6), Right([b4], 6) + 1)
End Sub

 

[Rick Rothstein]

This only works for fields containing numeric only. My field is alphanumeric.

Did you try it? If the last six characters of your field are digits (like you showed in your original message), it will work.