D
dan dungan
Hi,
Using Excel 2000, userforms, vba and sql, I'm creating a quoting
application for an electrical parts manufacturer. I've developed some
userforms, and I have a
code sample (below) that uses vlookup to pull the price from the price
table.
(Thanks to Dave Peterson.)
The quantity column is hard coded in the vlookup in Dave's sample.
I need to incorporate the quantities, delivery times and adders to the
code.
So I imagine the user will need to enter the part number in a textbox,
choose the
proper formula from a dropdown, enter any criteria that isn't shown in
the price
formula, and press a command button to calculate the prices. Then hold
the data
and recalculate for each requested quantity and delivery time, i.e., 8
weeks.
I've got 32 textboxes to hold the adder information.
I'm getting confused how to design the vba to calculate
all the prices for a quote. I think I need a vlookup for each adder.
I have been researching vba control structures
for the last two weeks to understand how to code this quote pricing.
I've searched the this forum for "Control structures", and found
several resources
like: http://www.aspfree.com/c/a/VB.NET/Programming-Fundamentals-Using-VBA/4/
Naming conventions: http://support.microsoft.com/kb/110264
Control structure ideas:
http://www.microsoft.com/office/com...97ca0d-da75-416e-8f1d-e9bfba487337&sloc=en-us
I'm still not clear how to organize the code for this project.
Consequently, I seek
feedback to understand possible control structure.
Thanks for your suggestions,
Dan
----------------------------------------------------------------------
Here are the rules, as I know them now:
1. Each quote has one quote number--assigned by the system.
2. A quote can contain pricing for an unlimited amount of part
numbers.
2. Pricing for a part number is determined by a pricing formula.
a. the core part price
b. adder prices-there could be several
c. the markup percentage
d. the setup cost
The pricing formulas are held in a spreadsheet--tblFormula.
Each formula resides on a row in the spreadsheet.
The required adders reside in cells on that row. The adders
indicate which
price list to use for the components of the part.
3. The price list worksheets follow the same format where the first
cell
in the row tells the criteria and the other cells hold the prices
for
each price break.
a. tblPriceListCorePart
tblPriceListAdderEntry
tblPriceListSelfLock
tblPriceListMod
tblPriceListClamp
b. some price lists don't have price breaks--their prices a just
one column
and applied regardless of the quantity.
tblConnectorCode
tblChain
4. Each part number can have several quantites
a. the price breaks
1-9
10-19
20-49
50-99
100-249
250-499
500-999
1000-2499
2500-4999
5000 & UP
Code Sample
___________________________________________________________
Private Sub cmdCalc_Click()
'From: Dave Peterson <[email protected]>
'Date: Thu, 25 Feb 2010 21:06:05 -0600
Dim sCoreAdapShell As String 'it's all text, right?
Dim res As Variant 'could be an error
sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text
res = Application.vLookup(sCoreAdapShell, _
ThisWorkbook.Worksheets("tblPriceListCorePart").Range("tblPriceListCore"),
_
5, False)
If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = res * Me.txtCore_Multiplier.Value
End If
End Sub
Using Excel 2000, userforms, vba and sql, I'm creating a quoting
application for an electrical parts manufacturer. I've developed some
userforms, and I have a
code sample (below) that uses vlookup to pull the price from the price
table.
(Thanks to Dave Peterson.)
The quantity column is hard coded in the vlookup in Dave's sample.
I need to incorporate the quantities, delivery times and adders to the
code.
So I imagine the user will need to enter the part number in a textbox,
choose the
proper formula from a dropdown, enter any criteria that isn't shown in
the price
formula, and press a command button to calculate the prices. Then hold
the data
and recalculate for each requested quantity and delivery time, i.e., 8
weeks.
I've got 32 textboxes to hold the adder information.
I'm getting confused how to design the vba to calculate
all the prices for a quote. I think I need a vlookup for each adder.
I have been researching vba control structures
for the last two weeks to understand how to code this quote pricing.
I've searched the this forum for "Control structures", and found
several resources
like: http://www.aspfree.com/c/a/VB.NET/Programming-Fundamentals-Using-VBA/4/
Naming conventions: http://support.microsoft.com/kb/110264
Control structure ideas:
http://www.microsoft.com/office/com...97ca0d-da75-416e-8f1d-e9bfba487337&sloc=en-us
I'm still not clear how to organize the code for this project.
Consequently, I seek
feedback to understand possible control structure.
Thanks for your suggestions,
Dan
----------------------------------------------------------------------
Here are the rules, as I know them now:
1. Each quote has one quote number--assigned by the system.
2. A quote can contain pricing for an unlimited amount of part
numbers.
2. Pricing for a part number is determined by a pricing formula.
a. the core part price
b. adder prices-there could be several
c. the markup percentage
d. the setup cost
The pricing formulas are held in a spreadsheet--tblFormula.
Each formula resides on a row in the spreadsheet.
The required adders reside in cells on that row. The adders
indicate which
price list to use for the components of the part.
3. The price list worksheets follow the same format where the first
cell
in the row tells the criteria and the other cells hold the prices
for
each price break.
a. tblPriceListCorePart
tblPriceListAdderEntry
tblPriceListSelfLock
tblPriceListMod
tblPriceListClamp
b. some price lists don't have price breaks--their prices a just
one column
and applied regardless of the quantity.
tblConnectorCode
tblChain
4. Each part number can have several quantites
a. the price breaks
1-9
10-19
20-49
50-99
100-249
250-499
500-999
1000-2499
2500-4999
5000 & UP
Code Sample
___________________________________________________________
Private Sub cmdCalc_Click()
'From: Dave Peterson <[email protected]>
'Date: Thu, 25 Feb 2010 21:06:05 -0600
Dim sCoreAdapShell As String 'it's all text, right?
Dim res As Variant 'could be an error
sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text
res = Application.vLookup(sCoreAdapShell, _
ThisWorkbook.Worksheets("tblPriceListCorePart").Range("tblPriceListCore"),
_
5, False)
If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = res * Me.txtCore_Multiplier.Value
End If
End Sub