Incremental Numbers

[DPatrick]

I had help creating Spreadsheet years ago with a drop down box with car models. Each model has a stock number that automatically increases when vehicles are added. We had two new models come out and I modified the Name Manager file to add those and I can't get the sheet to assign stock numbers now. Here's the original Macro, but I'm not sure if it's a Macro problem or Name Manager problem.


Sub Increment_ModelNumber()
Dim ModelName As String
Dim ModelNum As String
Dim Prefix As String
Dim Suffix As String



ModelName = ActiveCell.Offset(0, 1).Value
ModelNum = Names(ModelName).Value



Prefix = Mid(ModelNum, 3, InStr(1, ModelNum, "-") - 2)
Suffix = Val(Mid(ModelNum, InStr(1, ModelNum, "-") + 1, 4)) + 1



Do Until Len(Suffix) = 4
Suffix = "0" & Suffix
Loop



ActiveCell.Value = Prefix & Suffix



Names(ModelName).Value = "=" & Chr(34) & Prefix & Suffix & Chr(34)
End Sub

 

[NARAYANK991]

Hi ,

Try this :

Sub Increment_ModelNumber()
    Dim ModelName As String, ModelNum As String, Prefix As String, Suffix As String

    ModelName = ActiveCell.Offset(0, 1).Value
    ModelNum = ThisWorkbook.Names(ModelName).Value

    Prefix = Mid(ModelNum, 3, InStr(1, ModelNum, "-") - 2)
    Suffix = Val(Mid(ModelNum, InStr(1, ModelNum, "-") + 1, 4)) + 1
'   Do Until Len(Suffix) = 4
'   Suffix = "0" & Suffix
'   Loop

    Suffix = Right("0000" & Suffix, 4)
    ActiveCell.Value = Prefix & Suffix

    ThisWorkbook.Names(ModelName).Value = "=" & Chr(34) & Prefix & Suffix & Chr(34)
End Sub

Narayan