Question

[Ace]

Hi Everyone,

I currently have a Select Case statement with 3 cases in it. First, if a numbered amount is less than or equal to 10 then a cerain price is applied, say $12.00. It is the second and third cases that I am having problems with. I want to set it up so that if the numbered amount is greater than 10 but less than 20 then the amount applied is $12.00 plus $.50 for every number over 10. For example, if the number was 11 then the amount would be $12.50 , 12 would be $13.00, etc. Once you get over 20 then the cost would be $12.00 plus $.25. Does anyone know an easy way to set this up? I would appreicate any help. Thank you.

[SurfDemon]

Howdy,

I haven't checked the calculations as I'm guessing that your problem stems from the syntax for the case statement. But this should work

    x = 1
    
    Select Case x
    Case Is <= 10
        Debug.Print "$12"
        
    Case Is > 10, Is <= 20
        Debug.Print "$" + CStr(12 + 0.5 * (x - 10))
        
    Case Is > 20
        Debug.Print "$" + CStr(12 + 0.25 * (x - 10))
        
    End Select

I hope this helps,

SD

[Ace]

Hi SurfDemon,

I guess I am a little confused. What does the x - 10 do in the equation. I want to add .50 to every number above 10. For examples, if the number is 11 then the amount would be 12.50, if the amount was 12 the the amount would be 13.00, etc. I cannot get your formula to work correctly.

[JamesM]

Does this work?

Function calPrice(num As Double) As Double
If num > 0 Then ' Error check To prevent neg numbers
    
    Select Case num
        Case Is <= 10
            calPrice = 12

        Case Is <= 20
            calPrice = 12 + Clng(num - 10) * 0.5

        Case Else
            calPrice = 12 + Clng(num - 10) * 0.25
    End Select
    
End If
End Function

Private Sub Command1_Click()
MsgBox FormatCurrency(calPrice(12), 2)
End Sub

[SurfDemon]

X is just the number of items sold. I was just using it to illustrate the case statement. I thought that was where your problem lay.

The statement


CStr(12 + 0.5 * (x - 10))

is just a formula. So if you replace X with 11 you get

12 +0.5 * (11-10)

which =

12 + 0.5 * 1

=

12 +0.5

= 12.5 the correct number.


If you aren't dealing with whole numbers, then see JamesM's response.

Cheers,

SD