Automatically Assign Stock Number By Model
OK, got it. The best way I can figure to do this is to save the
last-used stock number for each vehicle as a named range, then
increment the stock number by one each time you enter a new line. I
did this by adding to the "Worksheet Change" event code you have. The
code assumes there will always be a dash in the stock number, and that
4 numeric characters will always follow the dash.
Breaking it into steps:
1. MAKE A BACKUP COPY of your file to avoid catastrophic and
irrecoverable data loss.
2. Insert a named range, Camry, and assign it the value 16-3685. (Just
do one model for now for proof of concept; do the rest later when we
validate this is working.)
3. Copy this Worksheet Change code and paste it in over the existing
code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
End If
Target.Offset(0, -1).Select
Increment_ModelNumber
Target.Offset(1, 0).Select
Application.EnableEvents = True
End Sub
4. Note the Worksheet Change code calls a routine called
Increment_ModelNumber, the code for which follows. Copy this code and
paste it in a module as you would a regular macro, not as a
worksheet-triggered event.
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
5. Save the file, and enter a new line using Camry from the dropdown in
a column B cell. You should see the date and the next stock number
populate appropriately.
Let me know!
|