VBA - Serial Numbers

T

Toastis

Board Regular
Joined
Apr 20, 2011
Messages
65
I need some assistance creating a VBA code. I am pretty sure a standard formula won't work. Warehouse personnel will use this tracker. options would be enter a new tote or an old tote (old totes will already have numbers, new totes need a number). On the 2nd tab I associated the seriel number scheme applicable to each warehouse. I need code that will tell enter the next serial number and then cross it off the list once used, so to speak.

Any ideas?
[TABLE="width: 295"]
<tbody>[TR]
[TD]Warehouse[/TD]
[TD]New Or Old?[/TD]
[TD]Tote Serial Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]O[/TD]
[TD] 1234[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[TD] ?[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]







[TABLE="width: 432"]


<colgroup><col style="width: 48pt;" span="9" width="64">
<tbody>[TR]

[TD="width: 64, bgcolor: transparent"]1-PHX[/TD]

[TD="width: 64, bgcolor: transparent"]2-TUC[/TD]

[TD="width: 64, bgcolor: transparent"]3-CAL[/TD]

[TD="width: 64, bgcolor: transparent"]4-LAV[/TD]

[TD="width: 64, bgcolor: transparent"]6-SAF[/TD]

[TD="width: 64, bgcolor: transparent"]7-ELP[/TD]

[TD="width: 64, bgcolor: transparent"]8-CHV[/TD]

[TD="width: 64, bgcolor: transparent"]9-BLM[/TD]

[TD="width: 64, bgcolor: transparent"]10-ABQ[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10000[/TD]

[TD="bgcolor: transparent, align: right"]20000[/TD]

[TD="bgcolor: transparent, align: right"]30000[/TD]

[TD="bgcolor: transparent, align: right"]40000[/TD]

[TD="bgcolor: transparent, align: right"]60000[/TD]

[TD="bgcolor: transparent, align: right"]70000[/TD]

[TD="bgcolor: transparent, align: right"]80000[/TD]

[TD="bgcolor: transparent, align: right"]90000[/TD]

[TD="bgcolor: transparent, align: right"]50000[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10001[/TD]

[TD="bgcolor: transparent, align: right"]20001[/TD]

[TD="bgcolor: transparent, align: right"]30001[/TD]

[TD="bgcolor: transparent, align: right"]40001[/TD]

[TD="bgcolor: transparent, align: right"]60001[/TD]

[TD="bgcolor: transparent, align: right"]70001[/TD]

[TD="bgcolor: transparent, align: right"]80001[/TD]

[TD="bgcolor: transparent, align: right"]90001[/TD]

[TD="bgcolor: transparent, align: right"]50001[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10002[/TD]

[TD="bgcolor: transparent, align: right"]20002[/TD]

[TD="bgcolor: transparent, align: right"]30002[/TD]

[TD="bgcolor: transparent, align: right"]40002[/TD]

[TD="bgcolor: transparent, align: right"]60002[/TD]

[TD="bgcolor: transparent, align: right"]70002[/TD]

[TD="bgcolor: transparent, align: right"]80002[/TD]

[TD="bgcolor: transparent, align: right"]90002[/TD]

[TD="bgcolor: transparent, align: right"]50002[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10003[/TD]

[TD="bgcolor: transparent, align: right"]20003[/TD]

[TD="bgcolor: transparent, align: right"]30003[/TD]

[TD="bgcolor: transparent, align: right"]40003[/TD]

[TD="bgcolor: transparent, align: right"]60003[/TD]

[TD="bgcolor: transparent, align: right"]70003[/TD]

[TD="bgcolor: transparent, align: right"]80003[/TD]

[TD="bgcolor: transparent, align: right"]90003[/TD]

[TD="bgcolor: transparent, align: right"]50003[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10004[/TD]

[TD="bgcolor: transparent, align: right"]20004[/TD]

[TD="bgcolor: transparent, align: right"]30004[/TD]

[TD="bgcolor: transparent, align: right"]40004[/TD]

[TD="bgcolor: transparent, align: right"]60004[/TD]

[TD="bgcolor: transparent, align: right"]70004[/TD]

[TD="bgcolor: transparent, align: right"]80004[/TD]

[TD="bgcolor: transparent, align: right"]90004[/TD]

[TD="bgcolor: transparent, align: right"]50004[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10005[/TD]

[TD="bgcolor: transparent, align: right"]20005[/TD]

[TD="bgcolor: transparent, align: right"]30005[/TD]

[TD="bgcolor: transparent, align: right"]40005[/TD]

[TD="bgcolor: transparent, align: right"]60005[/TD]

[TD="bgcolor: transparent, align: right"]70005[/TD]

[TD="bgcolor: transparent, align: right"]80005[/TD]

[TD="bgcolor: transparent, align: right"]90005[/TD]

[TD="bgcolor: transparent, align: right"]50005[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10006[/TD]

[TD="bgcolor: transparent, align: right"]20006[/TD]

[TD="bgcolor: transparent, align: right"]30006[/TD]

[TD="bgcolor: transparent, align: right"]40006[/TD]

[TD="bgcolor: transparent, align: right"]60006[/TD]

[TD="bgcolor: transparent, align: right"]70006[/TD]

[TD="bgcolor: transparent, align: right"]80006[/TD]

[TD="bgcolor: transparent, align: right"]90006[/TD]

[TD="bgcolor: transparent, align: right"]50006[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10007[/TD]

[TD="bgcolor: transparent, align: right"]20007[/TD]

[TD="bgcolor: transparent, align: right"]30007[/TD]

[TD="bgcolor: transparent, align: right"]40007[/TD]

[TD="bgcolor: transparent, align: right"]60007[/TD]

[TD="bgcolor: transparent, align: right"]70007[/TD]

[TD="bgcolor: transparent, align: right"]80007[/TD]

[TD="bgcolor: transparent, align: right"]90007[/TD]

[TD="bgcolor: transparent, align: right"]50007[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10008[/TD]

[TD="bgcolor: transparent, align: right"]20008[/TD]

[TD="bgcolor: transparent, align: right"]30008[/TD]

[TD="bgcolor: transparent, align: right"]40008[/TD]

[TD="bgcolor: transparent, align: right"]60008[/TD]

[TD="bgcolor: transparent, align: right"]70008[/TD]

[TD="bgcolor: transparent, align: right"]80008[/TD]

[TD="bgcolor: transparent, align: right"]90008[/TD]

[TD="bgcolor: transparent, align: right"]50008[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent, align: right"]10009[/TD]

[TD="bgcolor: transparent, align: right"]20009[/TD]

[TD="bgcolor: transparent, align: right"]30009[/TD]

[TD="bgcolor: transparent, align: right"]40009[/TD]

[TD="bgcolor: transparent, align: right"]60009[/TD]

[TD="bgcolor: transparent, align: right"]70009[/TD]

[TD="bgcolor: transparent, align: right"]80009[/TD]

[TD="bgcolor: transparent, align: right"]90009[/TD]

[TD="bgcolor: transparent, align: right"]50009[/TD]

[/TR]


</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
The second table is the series of serial numbers for each terminal. The top row is the terminal identifier and immediately under is the range of serial numbers available. If they can be built into the VBA code, that's wonderful. The beginning number is how we will associate them to the terminals. So WH 1 starts ALWAYS with 1, WH 2 starts ALWAYS with 2, the only one different is 10 which will start with 5.

Length of the serial number is immaterial.
 
Upvote 0
Warehouse = Terminal in the first table - I haven't changed the headers. The terms are interchangeable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA to Collect Computer Attritbutes
Replies
3
Views
12K
jimrward
jimrward
T
  • Question Question
Select next number in a range for a specific selection
Replies
2
Views
364
Toastis
T
B
  • Question Question
Doingvlookup search for partial value of serial numbers
Replies
1
Views
665
VoG
VoG

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top