Untested, uncompiled. Watch for typos.

It looks like the input you're asking the user for is a quantity to order. I
would think that those could be whole numbers only (1, 2, 3, ...). No fractions
allowed.

So I changed that doubles to longs.

And since you're populating a textbox (I think), I bet you want a formatted
string (like currency???). So I used Format() in that last portion of the code.

Option Explicit
Private Sub cmdCalc_Click()

Dim sCoreAdapShell As String
Dim res As Variant
Dim lInp As Long
Dim WhichCol As Long
Dim myRng As Range
Dim myMax As Long
Dim myMin As Long

myMin = 1
myMax = 10000

sCoreAdapShell = txtCore.Text & txtAdap_Config.Text & txtShell.Text

lInp = CLng(Application.InputBox(Prompt:="Enter the quantity", _
Title:="Quantity", _
Type:=1))

If lInp < myMin _
Or lInp myMax Then
'msgbox "Not valid???"
Exit Sub
End If

'I like this as a variable, so it sticks out more and is easier to
'see/change.
Set myRng = ThisWorkbook.Worksheets("tblPriceListCorePart") _
.Range("tblPriceListCore")

WhichCol = 0
Select Case dInp
Case Is < 10: WhichCol = 5
Case Is < 21: WhichCol = 6
Case Is < 51: WhichCol = 7
Case Is < 101: WhichCol = 8
Case Is < 251: WhichCol = 9
Case Is < 501: WhichCol = 10
Case Is < 1001: WhichCol = 11
Case Is < 2501: WhichCol = 12
Case Is < 5001: WhichCol = 13
Case Is < 10001: WhichCol = 14
End Select

If WhichCol = 0 Then
'this shouldn't happen!
MsgBox "Contact Dan, there's a design error!"
Exit Sub
End If

res = Application.VLookup(sCoreAdapShell, myRng, WhichCol, False)

If IsError(res) Then
'like #n/a in excel
Me.txtShellEntrySum.Text = "not found!"
Else
Me.txtShellEntrySum.Value _
= Format((res * Me.txtCore_Multiplier.Value) _
+ ((res * Me.txtCore_Multiplier.Value) _
* txtMarkup.Value) + (txtSetup.Value / lInp), "#,###.00")
End If

End Sub

dan dungan wrote:

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

--

Dave Peterson