Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Dim Count, Demand, SalesPrice, LostSales, PathCost, I, J As Integer, Check As Boolean
- Dim DestinationName As String, TotalProfit As Double
- Function CalcProfit(DestinationName As String)
- Count = Range("PathAll").Columns(1).Rows.Count
- Demand = Application.WorksheetFunction.VLookup(DestinationName, Range("Dinfo"), 2, False)
- SalesPrice = Application.WorksheetFunction.VLookup(DestinationName, Range("Dinfo"), 3, False)
- LostSales = Application.WorksheetFunction.VLookup(DestinationName, Range("Dinfo"), 4, False)
- I = 1
- Check = False
- Do Until Check = True Or I > Count
- If Range("PathAll").Columns(3).Rows(I) = 1 And Range("PathAll").Columns(2).Rows(I) = DestinationName Then
- Check = True
- PathCost = Range("PathAll").Columns(5).Rows(I)
- End If
- I = I + 1
- Loop
- If Check = True Then
- CalcProfit = Demand * (SalesPrice - PathCost)
- Else
- CalcProfit = -(Demand * LostSales)
- End If
- End Function
- Sub EvalRouteProfit()
- Count = Range("Dinfo").Rows.Count
- For J = 1 To Count
- DestinationName = Range("Dinfo").Columns(1).Rows(J)
- TotalProfitValue = TotalProfitValue + CalcProfit(DestinationName)
- Next
- MsgBox "Your total route profit is " & TotalProfitValue, vbInformation, "Total profit"
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement