============================================================================================

Function FindMax(valueArray, nameArray) As String

    Dim maxVal As Integer
    Dim maxValIndex As Integer
    maxVal = 0
    maxValIndex = 0

    Dim i As Integer

    For i = LBound(valueArray) To UBound(valueArray)
        If valueArray(i) > maxVal Then
            maxVal = valueArray(i)
            maxValIndex = i
        End If
    Next i

    FindMax = nameArray(maxValIndex)

End Function

============================================================================================

Function FindMaxRange(valueRng As Range, nameRng As Range) As String

    Dim maxVal As Integer
    Dim maxIndex As Integer

    maxVal = 0
    maxIndex = 0

    Dim i As Integer

    For i = 1 To valueRng.Count
        If valueRng.Cells(i) > maxVal Then
            maxVal = valueRng.Cells(i)
            maxIndex = i
        End If
    Next i

    FindMaxRange = nameRng.Cells(maxIndex)

End Function

============================================================================================

Function printMovieData(title As String, arrayTopic, arrayOther)

    Debug.Print ("*****************" & title & "***************")

    Dim i As Integer

    For i = LBound(arrayTopic) To UBound(arrayTopic)
        Debug.Print (arrayTopic(i) & " :  " & arrayOther(i))
    Next i

    Debug.Print ("**********************************************")

End Function

============================================================================================

Function MovieWithLargestRental(rngMovie As Range, rngMovieRental As Range) As String

   MovieWithLargestRental = FindMaxRange(rngMovieRental, rngMovie)

End Function

============================================================================================

Function MostExpensiveMovieToMake(rngMovie As Range, rngMovieProduction As Range) As String

    MostExpensiveMovieToMake = FindMaxRange(rngMovieProduction, rngMovie)

End Function

============================================================================================

Function FindMovieProfit(nameRng As Range, productionCostRng As Range, boxOfficeRng As Range) As String

    Dim profit As Double
    Dim maxProfit As Double
    Dim maxProfitIndex As Integer
    Dim boxEarnings As Integer
    Dim prodCost As Integer

    maxProfit = 0
    maxProfitIndex = 0


    Dim i As Integer
    For i = 1 To nameRng.Count

        boxEarnings = boxOfficeRng.Cells(i)
        prodCost = productionCostRng.Cells(i)

        profit = (boxEarnings - prodCost) / prodCost * 100

        If profit > maxProfit Then
            maxProfit = profit
            maxProfitIndex = i
        End If

    Next i

    FindMovieProfit = nameRng(maxProfitIndex)

End Function


============================================================================================


Function FindMovieProfitByGenre(nameRng As Range, productionCostRng As Range, boxOfficeRng As Range, genreRng As Range, genre As String) As String

    Dim profit As Double
    Dim maxProfit As Double
    Dim maxProfitIndex As Integer
    Dim boxEarnings As Integer
    Dim prodCost As Integer

    maxProfit = 0
    maxProfitIndex = 0


    Dim i As Integer
    For i = 1 To nameRng.Count

        If StrComp(genreRng.Cells(i), genre, vbTextCompare) = 0 Then
            boxEarnings = boxOfficeRng.Cells(i)
            prodCost = productionCostRng.Cells(i)

            profit = (boxEarnings - prodCost) / prodCost * 100

            Debug.Print (nameRng.Cells(i) & " (" & genreRng.Cells(i) & ") - " & profit)


            If profit > maxProfit Then
                maxProfit = profit
                maxProfitIndex = i
            End If
        End If
    Next i

    FindMovieProfitByGenre = nameRng(maxProfitIndex)


End Function

============================================================================================