I'm trying to create a spreadsheet that will automatically assign
stock numbers to vehicle model names. So far I have created the sheet
with 5 columns:
STOCK NUMBER MODEL VIN ORIGIN DATE
The MODEL column is a drop down list of all 19 vehicle model names,
when a name is selected from the list the current date is added to
column E. I would like to have column A assign a sequential stock
number by model name also. We have specific stock numbers for each
model, like a CAMRY starts with 16- and a Tundra starts with 26-. If I
choose a Tundra from the dropdown list in the MODEL column can I have
the STOCK NUMBER column generate a stock number like 26-1258 and keep
them going sequentially by model?

How is the date added to col E- is it done by an "on change" event that
triggers a bit of code in the worksheet?

Also, if the current Tundra model is 26-1258 and the next entry is a
Camry, is the stock number 16-1259 or does each model need to be
tracked separtely (ie, by a separate sequential number)?

Then date is generated by this code:

Private Sub Worksheet_Change(ByVal Target As Range)
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
End Sub

If the last stock number I used for a Tundra was 26-1258 then the next
would need to be 26-1259. But for the Camry the it may be a number like
16-3685 then next would have to be 16-3686. They would need to go in
seqence and bound by the model name. I would have to start with the
last number we have used with our current setup. Is there a way to make
the numbers start with a certain number then go in sequence?

"Does each model need to be tracked separtely (ie, by a separate
sequential number)?" Yes, If I understand correctly it would need to be
tracked seperately.

Dave O wrote:
How is the date added to col E- is it done by an "on change" event that
triggers a bit of code in the worksheet?

Also, if the current Tundra model is 26-1258 and the next entry is a
Camry, is the stock number 16-1259 or does each model need to be
tracked separtely (ie, by a separate sequential number)?

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!

Hey Dave,
I've tried the code a few of times but keep getting a runtime error
when the script is trying to assign the stock number. Do you need to
look at the sheet?
thanks
Richard

Dave O wrote:
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!

mx315 wrote:
Hey Dave,
I've tried the code a few of times but keep getting a "Run-time error 1004. Application defined or object defined error" when the script is trying to assign the stock number. Do you need to look at the sheet?
thanks
Richard

Dave O wrote:
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!