Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number 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)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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)? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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.) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
Email sent. I'm not much of a Excel guy so I maybe doing something
wrong on the module part. Dave O wrote: 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.) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
Still no email at yahoo DOT calm...
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
I haven't gotten any email errors back, but I sent it again to cyclezen
at yahoo Dave O wrote: Still no email at yahoo DOT calm... |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
It's coming from a toyota account, it maybe going to spam folder.
mx315 wrote: I haven't gotten any email errors back, but I sent it again to cyclezen at yahoo Dave O wrote: Still no email at yahoo DOT calm... |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Assign Stock Number By Model
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. mx315 wrote: It's coming from a toyota account, it maybe going to spam folder. mx315 wrote: I haven't gotten any email errors back, but I sent it again to cyclezen at yahoo Dave O wrote: Still no email at yahoo DOT calm... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to automatically number rows in an excel worksheet | Excel Worksheet Functions | |||
Automatically generating a number range | Excel Worksheet Functions | |||
Automatically number multiple sheets | Excel Discussion (Misc queries) | |||
How can I assign a number to a string? | Excel Discussion (Misc queries) | |||
how do I put a a sequence number into colun A automatically witho. | Excel Discussion (Misc queries) |