Hi,

In Excel 2000, I'm calculating prices for several different
quantities.

A customer will request a quote for a part number and want the price
for 10, 100, 250 and 1000, for example.

My code gets the correct answer, but I'm wondering if there isn't a
better way to write it because the code has a bunch of duplicate
lines.

Thanks for your feedback and thanks to Dave Peterson for his help
getting me started.

Here's the code:

Private Sub cmdCalc_Click()

Dim sCoreAdapShell As String 'this combines the core part name, the
adapter
'configuration and the shell size to
create the
'lookup value to use in the vlookup
formula.
Dim res As Variant 'this will hold the results of the
vlookup formula
Dim sInp As String 'quantity entered in inputbox
Dim dInp As Double 'this holds the quantity as a value not
a string

sCoreAdapShell = txtCore.text & txtAdap_Config.text & txtShell.text

sInp = InputBox("Enter the quantity", "Quantity")
dInp = sInp

If dInp < 0 Then

Select Case dInp
Case 1 To 10
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("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) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 10 To 20
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
6, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 20 To 50
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
7, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 50 To 100
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
8, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 100 To 250
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
9, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 250 To 500
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
10, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 500 To 1000
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
11, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 1000 To 2500
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
12, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 2500 To 5000
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
13, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case 5000 To 10000
res = Application.VLookup(sCoreAdapShell, _

ThisWorkbook.Worksheets("tblPriceListCorePart").Ra nge("tblPriceListCore"),
_
14, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.text = "not found!"
Else
Me.txtShellEntrySum.Value = (res *
Me.txtCore_Multiplier.Value) + ((res * Me.txtCore_Multiplier.Value) *
txtMarkup.Value) + (txtSetup.Value / dInp)
End If
Case Else
sInp = "too large"
End Select
End If
End Sub