Automatically Assign Stock Number By Model

[mx315]

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?

 

[Dave O]

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)?

 

[mx315]

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]

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]

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

 

[mx315]

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]

I'd be glad to take a look- please send it to cyclezen ATSIGN yahoo
DAHT com which is my blind email address. I will disregard any data
you send, but make sure your boss would be happy with you sending
company data to someone outside the company. (You might mock up sample
data if this is a problem.)

 

[mx315]

Email sent. I'm not much of a Excel guy so I maybe doing something
wrong on the module part.

 

[Dave O]

Still no email at yahoo DOT calm...

 

[mx315]

I haven't gotten any email errors back, but I sent it again to cyclezen
at yahoo

 

[mx315]

It's coming from a toyota account, it maybe going to spam folder.

 

[Dave O]

Hi, Richard-
For some reason it took forever for your email to arrive.

It looks like you missed a step: my idea is to store the last-used
stock number as a named range in Excel. A named range is sort of like
a variable in a programming language: a memory storage depot that holds
a certain value.

In your sprdsht if you'll click ~Insert ~Name ~Define, a window will
appear. Enter Camry in the "Names in Worksheet" box and in the "Refers
to" box type 16-3685 (or something else appropriate), then save and
close Excel entirely and re-open. The reason for that is a long-winded
Excel explanation that I'll be happy to go into: the code I sent you
turns event-triggered code off while it runs to avoid inadvertent
program looping. When the program generated an error it was after the
"event triggered code set to off" line and before the "event triggered
code turned back on" instruction, so no event-driven code would ever
run until Excel shut down and reset itself.

Then try entering a new line using your dropdown box in column B. It's
important that the named range you create is spelled exactly as it is
spelled in your dropdown list.

Making that change should work for you: you can then enter the
remaining vehicle models and named ranges (again, spelling is critical)
and their associated last-used stock numbers.

Dave O

 

[mx315]

Dave,
I'm running into a problem when I'm adding vehicles to the log.
Everything is working well except when I try to add the VIN number to
column C or when I try to add the origin code in column D I get the
runtime error again and it all stops working.