Macro to increase by 1 digit with a number in the format "AB36-12345"

[gaztr1x]

Hi, I am trying to create a button to insert a new row into a spreadsheet, automatically generating a new number 1 digit higher than the last. The number in cell A1 is in the format "AB36-12345" and I would like the new row to have the number as "AB36-12346." Alternatively if this is not possible, is there a macro that I can apply to the spreadsheet (already has 1000s of entries with this number format) to split column A to have, "AB36" in cell A1 and "12345" in cell B1. I could then revisit the button option with a +1 solely on column B and "12345". Any help would be good. Thanks

 

[RickXL]

Hi,

In circumstances like this I usually choose to use a right-click method rather than a button. In which case my macro, which needs to be associated with the worksheet not a module, looks like this:

Private Sub Worksheet_BeforerightClick(ByVal Target As Range, Cancel As Boolean)
    Dim id As Variant
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Cells(Rows.Count, 1).End(xlUp)(2, 1)) Is Nothing Then Exit Sub
    id = Split(Target.Offset(-1, 0).Value, "-")
    Target.Value = id(0) & "-" & id(1) + 1
    Cancel = True
End Sub

If you paste that macro into the place where woeksheet macros go then when you right-click the cell under the last entered id it will automatically insert an incremented one in the next cell. (I have assumed that the number will be in column A.) Right-clicking anywhere else will work as normal.

 

[gaztr1x]

Hi RickXL, Thanks for that. I like the way the right click works. However due to the way this spreadsheet is used (by many users) the numbers are counting down from highest to lowest. Can your code be altered to insert the new row at the top of the spreadsheet?

 

[RickXL]

Hi,

Yes, things can be simplified a bit if you know where the right-click is going to happen. I have assumed A1 here but you can replace that with any cell now.

Private Sub Worksheet_BeforerightClick(ByVal Target As Range, Cancel As Boolean)
    Dim id As Variant
    If Target.Address <> Range("A1").Address Then Exit Sub  ' <-- Cell to click
    id = Split(Range("A2").Value, "-")
    Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A2").Value = id(0) & "-" & id(1) + 1
    Cancel = True
End Sub

 

[gaztr1x]

Hi RickXL, in the end I have actually ended up using some of this within a button, but you have been a great help. Thanks.

 

[RickXL]

No problem - whatever works for you.

Thanks for the feedback.