Multiplying select case variables

[Deuce Bucksman]

Quick Question: does anyone know how to use a formula within a select case structure? Essentially, i'm trying to make the below case strucutre to calculate commisions based on the sales input. Everytime I try to multiply the variable comish into the Case value (14%), it keeps giving me an error. Any advice? Thanks!

Function Commision()
Dim Comish
Comish = 25000

Case1 = 0.08
Case2 = 0.1
Case3 = 0.12
Case4 = 0.14

Select Case Comish
Case 0 To 5000: commish (Case1)
Case 5001 To 10000: commish (Case2)
Case 10001 To 15000: commish (Case3)
Case Else: commish (Case4)
End Select
End Function

 

[shg]

What is that supposed to do?

Maybe this:

Function Commission(Amount As Double) As Double
    If Amount >= 0 Then
        Commission = WorksheetFunction.Lookup(Amount, _
                                              Array(0, 5001, 10001, 15001), _
                                              Array(0.08, 0.1, 0.12, 0.14))
    End If
End Function

 

[Deuce Bucksman]

The objective is to create a function that will automatically multiply the correct percentage against the number dropped into the formula. I just couldnt figure out how to multiply in a select case structure, but i'm going to give your alternative a go. Thanks!

 

[shg]

Then

Function Commission(Amount As Double) As Double
    If Amount >= 0 Then
        Commission = Amount * WorksheetFunction.Lookup(Amount, _
                                              Array(0, 5001, 10001, 15001), _
                                              Array(0.08, 0.1, 0.12, 0.14))
    End If
End Function

=Comission(10000) returns 1000

 

[tusharm]

The multiplication operator in VBA is *. OTOH, VBA doesn't understand Comish (Case1) in the context in which it is written.

Quick Question: does anyone know how to use a formula within a select case structure? Essentially, i'm trying to make the below case strucutre to calculate commisions based on the sales input. Everytime I try to multiply the variable comish into the Case value (14%), it keeps giving me an error. Any advice? Thanks!

Function Commision()
Dim Comish
Comish = 25000

Case1 = 0.08
Case2 = 0.1
Case3 = 0.12
Case4 = 0.14

Select Case Comish
Case 0 To 5000: commish (Case1)
Case 5001 To 10000: commish (Case2)
Case 10001 To 15000: commish (Case3)
Case Else: commish (Case4)
End Select
End Function

 

[Deuce Bucksman]

Thanks! everytime I used the * multiplier it gave me an error. What I did was added another variable "bonus" and used an = sign to connect comish and the different cases. So now it works. Thanks for the collaborative effort. I appreciate everyones help!