Help on a function.
Posted by Chris on September 18, 2000 8:30 AM
Can someone review the attahced code and help me out with my error? My feeling is that I am not defining something correctly, I just can't place my finger on it.
I am trying to write a user-defined function that returns the maximum sale units for a group of Sales Reps.
There are two columns of detailed data (Sales Rep and Units) for each sale and a Sales Rep Key (in an array) that may change over time.
I first need to aggregate the sale units by Sales Rep (i.e., SUMIF statement) and then select the highest number of units sold.
My goal is to acheive this calculation within the function without having to set-up a separate table in the worksheet.
Dim RepTable() As Variant
Dim RepCount As Integer
Dim RepTotal() As Variant
Dim i As Integer
Dim n As Integer
For n = 1 to RepCount
RepTable(n) = ActiveCell.Offset(n - 1, 0)
For n = 1 To RepCount
For i = 1 To RepCount
If Rep(i) = RepTable(n) Then
RepTotal(n) = RepTotal + Units
MaxSales = Application.WorksheetFunction.Max(RepTotal)