Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the logic of a complex task
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rather complex logic statement | Excel Worksheet Functions | |||
a complex task | Excel Discussion (Misc queries) | |||
Complex programming task | Excel Programming | |||
Complex lookup task for a newbies | Excel Worksheet Functions | |||
Logic to Determine if a Data Pivot Field Exist? | Excel Programming |