select case simplification request

[dan dungan]

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

ThisWorkbook.Worksheets("tblPriceListCorePart").Range("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").Range("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").Range("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").Range("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").Range("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").Range("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").Range("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").Range("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").Range("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]

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]

Wow. Thanks Dave.

I'm getting ready to go home, but I'll look at this first thing on
Monday.

Thanks again,

Dan

 

[Rick Rothstein]

I am pretty sure that the following macro does what your macro does...

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
Dim dInpFactor As Long 'this holds the 3rd argument to VLookUp

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

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

If dInp > 10000 Then
sInp = "too large"
ElseIf dInp > 0 Then
dInpFactor = 5 - (dInp > 10) - (dInp > 20) - (dInp > 50) - _
(dInp > 100) - (dInp > 250) - (dInp > 500) - _
(dInp > 1000) - (dInp > 2500) - (dInp > 5000)
res = Application.VLookup(sCoreAdapShell, ThisWorkbook.Worksheets( _
"tblPriceListCorePart").Range( _
"tblPriceListCore"), dInpFactor, False)
If IsError(res) Then
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
End If
End Sub

 

[dan dungan]

Hi Dave,

Your code works great.

I'm getting some errors when Me.txtShellEntrySum.Text = "not found!.

In other code, I'm getting errors when I hide or show different
userforms.

I need to gather more facts to see what is going on.

Thanks for your help.

Dan

 

[dan dungan]

Hi Rick,

Thank you, rick. Your code works, as well.

I'm not sure which code to use.

Is there some advantage to either approach?

thanks again,

Dan

 

[Rick Rothstein]

I doubt there is an advantage using either approach other than readability.
I tend to like more compact code, so I don't really like a long string of
Case statements (although I will admit they are easier for most people to
read)... my approach was to sum up the True/False results from the logical
comparisons of the dInp variable against the various range limits for it.
Just so you know, True in the VBA world equates to negative one (-1) and NOT
plus one (+1) like it does in worksheet formulas... this is why I have minus
signs in front of all my logical expressions... it turns the minus ones from
the True logical expressions to plus values (minus a minus value is a plus
value... it's like multiplying minus one times the minus value, which is
also minus one in the case of True... minus one times zero is still zero).
Anyway, if it is easier for you to see what is going on using the string of
Case statements, then that may be the way you should go... your choice, of
course.

 

[dan dungan]

Thanks for your explanations, Rick

Dan