coolbear90
New Member
- Joined
- Jul 2, 2015
- Messages
- 2
Hi,
We're trying to develop a better identification system at my work for tracking batches of plant deliveries. The codes we want to use will tell us at a glance what plant species it is, what supplier we got it from, and end with a 3 digit number that increases with each repeated delivery. I have already stored reference codes for each supplier and plant to draw on for this purpose. I also want it to begin with a "P" to identify that it's a plant (sounds silly, but it's for database purposes later on).
I want to generate batch references via a vba userform, where the user selects the plant and the supplier from combo boxes (row sourced back to the spreadsheet), and the code uses this info to pick out the unique reference numbers and determine the BaseCode (PPLA01SUP), checks if the default 001 ending number already exists for that BaseCode, and increases the number by 1 if it does (i.e. to 002). The final batch reference appears in a textbox on the userform.
So for our first delivery of 'Plant 1' (PLA01), ordered from 'Supplier' (SUP), the format would be: PPLA01SUP001
For our second delivery of the same order it would be PPLA01SUP002.
But for our first delivery of 'Plant 2' (PLA02), it would be PPLA02SUP001.
I have already made code to generate the BaseCode (PPLA01SUP), but I can't get this to then look through the existing batch numbers and increase the 3 digit number by 1 if that specific batch number already exists.
To clarify, I have lists on separate sheets for plants (Sheet1), suppliers (Sheet2) and batch numbers (Sheet3).
Below is the code I have so far. For Sheets 1 and 2, the name of plant/supplier is in column A and the corresponding reference number in column B.
Private Sub cboSupplier_AfterUpdate()
Dim p As String, s As String, u As String, BaseCode As String, BatchRef As String, i As Integer, c As Integer
'Lookup plant and supplier reference numbers
Dim foundp As Range, founds As Range,
Set foundp = Sheet1.Columns("A").Find(what:=Me.cboPlant.Value, LookIn:=xlValues, lookat:=xlWhole)
Set founds = Sheet2.Columns("A").Find(what:=Me.cboSupplier.Value, LookIn:=xlValues, lookat:=xlWhole)
p = foundp.Offset(0, 1).Value
s = founds.Offset(0, 1).Value
BaseCode = "P" & p & s
'And now bit adding the 3-digit number on the end - this is where I'm stuck
c = 0
i = 1
u = "00" & i
BatchRef = BaseCode & u
With Sheet3
Do While Sheet3.Cells(c + 1, 1).Value <> ""
If Sheet3.Cells(c + 1, 1).Value = BatchRef Then
i = i + 1
Me.tbBatch.Value = BatchRef
Else
i = 1
Me.tbBatch.Value = BatchRef
End If
c = c + 1
Loop
End With
End Sub
The above code all works, except it won't go above 001. To be clear, I have already put a PPLA01SUP001 in Sheet 3 Column A to test with.
Is what I'm trying to do actually possible, or am I just trying to be too complicated? The extent of my vba knowledge comes from what I've picked up from the internet over the past few months, so forgive me if I'm missing something obvious!
Any help would be appreciated. Thanks!
We're trying to develop a better identification system at my work for tracking batches of plant deliveries. The codes we want to use will tell us at a glance what plant species it is, what supplier we got it from, and end with a 3 digit number that increases with each repeated delivery. I have already stored reference codes for each supplier and plant to draw on for this purpose. I also want it to begin with a "P" to identify that it's a plant (sounds silly, but it's for database purposes later on).
I want to generate batch references via a vba userform, where the user selects the plant and the supplier from combo boxes (row sourced back to the spreadsheet), and the code uses this info to pick out the unique reference numbers and determine the BaseCode (PPLA01SUP), checks if the default 001 ending number already exists for that BaseCode, and increases the number by 1 if it does (i.e. to 002). The final batch reference appears in a textbox on the userform.
So for our first delivery of 'Plant 1' (PLA01), ordered from 'Supplier' (SUP), the format would be: PPLA01SUP001
For our second delivery of the same order it would be PPLA01SUP002.
But for our first delivery of 'Plant 2' (PLA02), it would be PPLA02SUP001.
I have already made code to generate the BaseCode (PPLA01SUP), but I can't get this to then look through the existing batch numbers and increase the 3 digit number by 1 if that specific batch number already exists.
To clarify, I have lists on separate sheets for plants (Sheet1), suppliers (Sheet2) and batch numbers (Sheet3).
Below is the code I have so far. For Sheets 1 and 2, the name of plant/supplier is in column A and the corresponding reference number in column B.
Private Sub cboSupplier_AfterUpdate()
Dim p As String, s As String, u As String, BaseCode As String, BatchRef As String, i As Integer, c As Integer
'Lookup plant and supplier reference numbers
Dim foundp As Range, founds As Range,
Set foundp = Sheet1.Columns("A").Find(what:=Me.cboPlant.Value, LookIn:=xlValues, lookat:=xlWhole)
Set founds = Sheet2.Columns("A").Find(what:=Me.cboSupplier.Value, LookIn:=xlValues, lookat:=xlWhole)
p = foundp.Offset(0, 1).Value
s = founds.Offset(0, 1).Value
BaseCode = "P" & p & s
'And now bit adding the 3-digit number on the end - this is where I'm stuck
c = 0
i = 1
u = "00" & i
BatchRef = BaseCode & u
With Sheet3
Do While Sheet3.Cells(c + 1, 1).Value <> ""
If Sheet3.Cells(c + 1, 1).Value = BatchRef Then
i = i + 1
Me.tbBatch.Value = BatchRef
Else
i = 1
Me.tbBatch.Value = BatchRef
End If
c = c + 1
Loop
End With
End Sub
The above code all works, except it won't go above 001. To be clear, I have already put a PPLA01SUP001 in Sheet 3 Column A to test with.
Is what I'm trying to do actually possible, or am I just trying to be too complicated? The extent of my vba knowledge comes from what I've picked up from the internet over the past few months, so forgive me if I'm missing something obvious!
Any help would be appreciated. Thanks!
