CODEWITHSUNDEEP
vbaexcel
Edward Rhoades
Edward Rhoades

Reputation: 31

VBA - How to calculate a tiered value

I am trying to find out how much we will be charged storage. The company we are dealing with is using a tiered pricing strategy to charge more based upon the number of days we store equipment.

The tiered amounts are these: Day 1-4 = $100, Day 5-8 = $150, Day 9+ = $200. I will need to calculate the amounts per day. So, if a rental goes 3 days I will need to calculate 3 days at 100/day = $300. If rental goes 8 days, then I will need to calculate the first 4 days at $400 total + the next 4 days at 150 each = 600 for a grand total of $1000.

I have a count of the number of days. How can I get a total cost from the amount of rental days used?

Upvotes: 0

Views: 393

Answers (1)

jcarroll
jcarroll

Reputation: 577

The below will prompt for an input, and return a messagebox with the answer. Also, you say 8 days should return 850, but do you mean 1000? If not let me know and I will update.

    Sub calculateCost()

    Dim intDays As Variant
    Dim dblCost As Double
    intDays = InputBox("Please type in number of days")

    If Not IsNumeric(intDays) Then
        MsgBox "Please type only numbers"
        Exit Sub
    End If

    Select Case intDays
        Case 1, 2, 3, 4
            dblCost = intDays * 100
        Case 5, 6, 7, 8
            dblCost = 400 + (intDays - 4) * 150
        Case Is > 8
            dblCost = 1000 + (intDays - 8) * 200
        Case Else
            dblCost = 0
    End Select

        MsgBox "Total cost is " & dblCost
End Sub

Upvotes: 1

Related Questions