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