D
dan dungan
Hi,
My task is create version 2 of a quoting spreadsheet which will
parse a part number and determine the price for a requested quantity
of that part number.
First, I describe our price sheets
Next, I discuss the part number categories
Then, I show a Boolean part number category representation.
Finally, I show the code I'm developing.
I'm asking for help to determine the logic for a plan.
+++++++++++++++++++++++++++++++++++++++++++++
In version 1 which is in use now, we pull prices from worksheets that
hold the prices for different sizes and price breaks.
We have worked out the prices for certain parts. One example is the
Basic Adapter Number 217. The owner examines a new request from a
customer and writes what he calls a formula.
He'll say use the 217 x 2. These price sheets are organized
by price breaks and size like this:
+++++++++++++++++++++++++++++++++++++++++++
BAN Style Order 1-9 10-19 20-50
Num
217 S 06 $3.00 $2.50 $2.00
217 S 08 $3.50 $3.00 $2.50
217 S 10 $4.00 $3.50 $3.00
+++++++++++++++++++++++++++++++++++++++++++
I'm using vlookups to return the prices to a quote form.
Our end users follow the blueprint and the owners "formula", which
contains reminder notes, to put together a price.
The owner wants version 2 to automate these choices.
For example:
We have a part number: A1816B35C12N0621
Category
Basic Adapter Number
Proprietary Part Series AXX16
Function Designator A
Connector Code 18
Series Part Number 16
Adapter Style S,B
Accessory Order Number 35
Accessory Order Number
-opposite end
Cable Entry Order Number
Termination Number
Entry Style Option
Accessories Option
Shield Option
Shield Length Code
Chain & Potting Provision Option 0,1,2,4,C
Chain & Wire Rope Length Option 12
Bushing Option
Drain Hole Option
Cable Strain Relief Style
Gland and O ring Material Option B,S,N
Length Code Number 06
Spin Coupling Option
Self-Locking Coupling Nut
Plating Code 21
Mod Code X
So I'm using the following code.
The portion for 217 is working pretty well, but the E and A portion is
confusing.
I made a boolean representation to try to understand the categories of
part numbers. Here is a sample:
Boolean Proprietary
Category Part Series or
Representation Basic Adapter
Number (BAN)
0111111000000011000110011 AXX16
0111111010000000001000011 EXX40
EXX41
0111111010000000001011011 EXX03
0111111010000000001100011 AXX30
EXX13
EXX30
EXX36
EXX38
0111111010000000001110011 AXX01
EXX01
EXX04
EXX05
EXX07
EXX15
EXX17
EXX21
EXX34
0111111010000000010100011 AXX08
0111111010000000100000011 AXX14
0111111010001100000100011 EXX11
EXX32
0111111010010000000110011 EXX19
0111111010100000000110011 EXX25
0111111100000000000011011 AXX03
1000011000000000000000010 901-423
1000011000000000100000010 901S432
1001001010000000000110011 265
1001001010000000000110110 266
1001011010000000000010010 247
1001011010000000000010110 248
293
1001011010000000000100011 267
1001011010000000001100010 230
1001011011000000000010011 217
1001011011000000000010110 270
271
++++++++++++++++++++++++++++++++++++++++++
Private Sub cmdGetPrice_Click()
Dim Parts As String
'BAN stands for Basic Adapter Number
Dim BAN As Variant
Dim BasicPartNo As String
Dim FuncDesignator As String
Dim ConnectorCode As String
Dim SeriesPartNo As String
Dim RayFormula As String
Dim Style As String
Dim OrderNum As String
Dim ShellSize As String
Dim EntrySize As String
Dim Clamp As String
Dim Gland As String
Dim sAlph As String
Dim sLen As String
Dim sTerm As String
Dim SPlate As String
Dim myRange As Range
Dim myDestRng As Range
Dim R As Range
Dim lRow As Long
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set myRange = aWS.Range("A2") '<~~~First cell of range
lRow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row
Set myRange = myRange.Resize(lRow - myRange.Row + 1, 1)
For Each R In myRange
'Debug.Print r.Address, r.Text,
If Mid(R.Text, 4, 1) = "-" Then
BasicPartNo = Left(R.Text, 7)
Debug.Print "Basic Part Number: "; BasicPartNo
ElseIf Left(R.Text, 1) = "E" Or Left(R.Text, 1) = "A" Then 'If
it's other than E or A, this won't work.
FuncDesignator = Left(R.Text, 1)
ConnectorCode = Mid(R.Text, 2, 2)
SeriesPartNo = Mid(R.Text, 4, 2)
Style = Mid(R.Text, 6, 1)
RayFormula = FuncDesignator & "XX" & SeriesPartNo & Style
OrderNum = Mid(R.Text, 7, 2)
EntrySize = Mid(R.Text, 9, 2)
Clamp = Mid(R.Text, 11, 1)
Gland = Mid(R.Text, 12, 1)
sLen = Mid(R.Text, 13, 2)
SPlate = Mid(R.Text, 15, 2)
With Worksheets("A&E")
Set myDestRng = .Range("D2")
.Range("D2") = RayFormula
Set myDestRng = .Range("E2")
.Range("E2") = ConnectorCode
Set myDestRng = .Range("F2")
.Range("F2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
Set myDestRng = .Range("J2")
.Range("J2") = Gland
Set myDestRng = .Range("K2")
.Range("K2") = sLen
Set myDestRng = .Range("L2")
.Range("L2") = SPlate
End With
'End
' Debug.Print RayFormula
' Debug.Print "FuncDesignator: "; FuncDesignator, _
' "Connector Code: "; ConnectorCode, _
' "SeriesPartNo: "; SeriesPartNo, _
' "Formula: "; RayFormula, _
' "Adapter Style: "; Style
Else
BAN = Left(R.Text, 3)
'Debug.Print "BAN: "; BAN, "Connector Code: ", ConnectorCode
ConnectorCode = Mid(R.Text, 4, 2)
sAlph = Mid(R.Text, 9, 1)
'Chr
Style = Mid(R.Text, 6, 1)
RayFormula = BAN & "XX" & Style
If InStr(1, "CAD-R", sAlph) Then
OrderNum = Mid(R.Text, 7, 3)
EntrySize = Mid(R.Text, 10, 2)
ConnectorCode = Mid(R.Text, 4, 2)
sLen = Mid(R.Text, 13, 2)
sTerm = Mid(R.Text, 12, 1)
SPlate = Mid(R.Text, 15, 2)
Else
OrderNum = Mid(R.Text, 7, 2)
ConnectorCode = Mid(R.Text, 4, 2)
EntrySize = Mid(R.Text, 9, 2)
sLen = Mid(R.Text, 12, 2)
sTerm = Mid(R.Text, 11, 1)
SPlate = Mid(R.Text, 14, 2)
'OrderNum = Mid(r.Text, 7, 3)
'ConnectorCode = Mid(r.Text, 4, 2)
End If
With Worksheets("217")
Set myDestRng = .Range("E2")
.Range("E2") = RayFormula
Set myDestRng = .Range("G2")
.Range("G2") = ConnectorCode
Set myDestRng = .Range("I2")
.Range("I2") = Style
Set myDestRng = .Range("K2")
.Range("K2") = OrderNum
Set myDestRng = .Range("q2")
.Range("q2") = EntrySize
Set myDestRng = .Range("r2")
.Range("r2") = sTerm
Set myDestRng = .Range("s2")
.Range("s2") = sLen
Set myDestRng = .Range("T2")
.Range("T2") = SPlate
End With
End If
cmdGetPrice.Visible = False
CmdNextPartNum.Visible = True
Application.Calculate
End
Next R
End Sub
My task is create version 2 of a quoting spreadsheet which will
parse a part number and determine the price for a requested quantity
of that part number.
First, I describe our price sheets
Next, I discuss the part number categories
Then, I show a Boolean part number category representation.
Finally, I show the code I'm developing.
I'm asking for help to determine the logic for a plan.
+++++++++++++++++++++++++++++++++++++++++++++
In version 1 which is in use now, we pull prices from worksheets that
hold the prices for different sizes and price breaks.
We have worked out the prices for certain parts. One example is the
Basic Adapter Number 217. The owner examines a new request from a
customer and writes what he calls a formula.
He'll say use the 217 x 2. These price sheets are organized
by price breaks and size like this:
+++++++++++++++++++++++++++++++++++++++++++
BAN Style Order 1-9 10-19 20-50
Num
217 S 06 $3.00 $2.50 $2.00
217 S 08 $3.50 $3.00 $2.50
217 S 10 $4.00 $3.50 $3.00
+++++++++++++++++++++++++++++++++++++++++++
I'm using vlookups to return the prices to a quote form.
Our end users follow the blueprint and the owners "formula", which
contains reminder notes, to put together a price.
The owner wants version 2 to automate these choices.
For example:
We have a part number: A1816B35C12N0621
Category
Basic Adapter Number
Proprietary Part Series AXX16
Function Designator A
Connector Code 18
Series Part Number 16
Adapter Style S,B
Accessory Order Number 35
Accessory Order Number
-opposite end
Cable Entry Order Number
Termination Number
Entry Style Option
Accessories Option
Shield Option
Shield Length Code
Chain & Potting Provision Option 0,1,2,4,C
Chain & Wire Rope Length Option 12
Bushing Option
Drain Hole Option
Cable Strain Relief Style
Gland and O ring Material Option B,S,N
Length Code Number 06
Spin Coupling Option
Self-Locking Coupling Nut
Plating Code 21
Mod Code X
So I'm using the following code.
The portion for 217 is working pretty well, but the E and A portion is
confusing.
I made a boolean representation to try to understand the categories of
part numbers. Here is a sample:
Boolean Proprietary
Category Part Series or
Representation Basic Adapter
Number (BAN)
0111111000000011000110011 AXX16
0111111010000000001000011 EXX40
EXX41
0111111010000000001011011 EXX03
0111111010000000001100011 AXX30
EXX13
EXX30
EXX36
EXX38
0111111010000000001110011 AXX01
EXX01
EXX04
EXX05
EXX07
EXX15
EXX17
EXX21
EXX34
0111111010000000010100011 AXX08
0111111010000000100000011 AXX14
0111111010001100000100011 EXX11
EXX32
0111111010010000000110011 EXX19
0111111010100000000110011 EXX25
0111111100000000000011011 AXX03
1000011000000000000000010 901-423
1000011000000000100000010 901S432
1001001010000000000110011 265
1001001010000000000110110 266
1001011010000000000010010 247
1001011010000000000010110 248
293
1001011010000000000100011 267
1001011010000000001100010 230
1001011011000000000010011 217
1001011011000000000010110 270
271
++++++++++++++++++++++++++++++++++++++++++
Private Sub cmdGetPrice_Click()
Dim Parts As String
'BAN stands for Basic Adapter Number
Dim BAN As Variant
Dim BasicPartNo As String
Dim FuncDesignator As String
Dim ConnectorCode As String
Dim SeriesPartNo As String
Dim RayFormula As String
Dim Style As String
Dim OrderNum As String
Dim ShellSize As String
Dim EntrySize As String
Dim Clamp As String
Dim Gland As String
Dim sAlph As String
Dim sLen As String
Dim sTerm As String
Dim SPlate As String
Dim myRange As Range
Dim myDestRng As Range
Dim R As Range
Dim lRow As Long
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set myRange = aWS.Range("A2") '<~~~First cell of range
lRow = aWS.Cells(aWS.Rows.Count, myRange.Column).End(xlUp).Row
Set myRange = myRange.Resize(lRow - myRange.Row + 1, 1)
For Each R In myRange
'Debug.Print r.Address, r.Text,
If Mid(R.Text, 4, 1) = "-" Then
BasicPartNo = Left(R.Text, 7)
Debug.Print "Basic Part Number: "; BasicPartNo
ElseIf Left(R.Text, 1) = "E" Or Left(R.Text, 1) = "A" Then 'If
it's other than E or A, this won't work.
FuncDesignator = Left(R.Text, 1)
ConnectorCode = Mid(R.Text, 2, 2)
SeriesPartNo = Mid(R.Text, 4, 2)
Style = Mid(R.Text, 6, 1)
RayFormula = FuncDesignator & "XX" & SeriesPartNo & Style
OrderNum = Mid(R.Text, 7, 2)
EntrySize = Mid(R.Text, 9, 2)
Clamp = Mid(R.Text, 11, 1)
Gland = Mid(R.Text, 12, 1)
sLen = Mid(R.Text, 13, 2)
SPlate = Mid(R.Text, 15, 2)
With Worksheets("A&E")
Set myDestRng = .Range("D2")
.Range("D2") = RayFormula
Set myDestRng = .Range("E2")
.Range("E2") = ConnectorCode
Set myDestRng = .Range("F2")
.Range("F2") = OrderNum
Set myDestRng = .Range("H2")
.Range("H2") = EntrySize
Set myDestRng = .Range("I2")
.Range("I2") = Clamp
Set myDestRng = .Range("J2")
.Range("J2") = Gland
Set myDestRng = .Range("K2")
.Range("K2") = sLen
Set myDestRng = .Range("L2")
.Range("L2") = SPlate
End With
'End
' Debug.Print RayFormula
' Debug.Print "FuncDesignator: "; FuncDesignator, _
' "Connector Code: "; ConnectorCode, _
' "SeriesPartNo: "; SeriesPartNo, _
' "Formula: "; RayFormula, _
' "Adapter Style: "; Style
Else
BAN = Left(R.Text, 3)
'Debug.Print "BAN: "; BAN, "Connector Code: ", ConnectorCode
ConnectorCode = Mid(R.Text, 4, 2)
sAlph = Mid(R.Text, 9, 1)
'Chr
Style = Mid(R.Text, 6, 1)
RayFormula = BAN & "XX" & Style
If InStr(1, "CAD-R", sAlph) Then
OrderNum = Mid(R.Text, 7, 3)
EntrySize = Mid(R.Text, 10, 2)
ConnectorCode = Mid(R.Text, 4, 2)
sLen = Mid(R.Text, 13, 2)
sTerm = Mid(R.Text, 12, 1)
SPlate = Mid(R.Text, 15, 2)
Else
OrderNum = Mid(R.Text, 7, 2)
ConnectorCode = Mid(R.Text, 4, 2)
EntrySize = Mid(R.Text, 9, 2)
sLen = Mid(R.Text, 12, 2)
sTerm = Mid(R.Text, 11, 1)
SPlate = Mid(R.Text, 14, 2)
'OrderNum = Mid(r.Text, 7, 3)
'ConnectorCode = Mid(r.Text, 4, 2)
End If
With Worksheets("217")
Set myDestRng = .Range("E2")
.Range("E2") = RayFormula
Set myDestRng = .Range("G2")
.Range("G2") = ConnectorCode
Set myDestRng = .Range("I2")
.Range("I2") = Style
Set myDestRng = .Range("K2")
.Range("K2") = OrderNum
Set myDestRng = .Range("q2")
.Range("q2") = EntrySize
Set myDestRng = .Range("r2")
.Range("r2") = sTerm
Set myDestRng = .Range("s2")
.Range("s2") = sLen
Set myDestRng = .Range("T2")
.Range("T2") = SPlate
End With
End If
cmdGetPrice.Visible = False
CmdNextPartNum.Visible = True
Application.Calculate
End
Next R
End Sub