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.
Function MaxSales(Units,Rep)
Application.Volatile True
Dim RepTable() As Variant
Dim RepCount As Integer
Dim RepTotal() As Variant
Dim i As Integer
Dim n As Integer
Sheets("RepKey").Select
Range("A1").Select
RepCount=Selection.End(xlDown).Cells.Count
ReDim RepTable(RepCount)
ReDim RepTotal(RepCount)
For n = 1 to RepCount
RepTable(n) = ActiveCell.Offset(n - 1, 0)
Next n
For n = 1 To RepCount
For i = 1 To RepCount
If Rep(i) = RepTable(n) Then
RepTotal(n) = RepTotal + Units
End If
Next i
Next n
MaxSales = Application.WorksheetFunction.Max(RepTotal)
Posted by Tim Francis-Wright on September 18, 0100 10:48 AM
You have a problem here with RepTotal:
you define it as an array, but then use it
as a scalar. There's a bigger problem
in that Rep(i) isn't defined. You have
assumed that RepCount equals the number of
rows in your table, which might not be the
case.
I humbly suggest that you use worksheet functions
for what you need to do. (Others may find
ways to avoid the kludge I use herein.)
I assume that you have ranges Unitrange
and Reprange that have one column each and
the same number of rows; and range
Unitrep, with one column and the number
of rows equal to the number of distinct reps.
Fill Repkey with the roster of reps.
(Here's the kludge:) One column to the right
of Repkey, have the cells count upwards
from 1.
To get the maximum sales by rep, use the array formula:
=MAX(SUMIF(reprange,repkey,unitrange))
To get the rep with the maximum sales, use the array formula:
=INDEX(repkey,MAX(IF(SUMIF(reprange,repkey,unitrange)=MAX(SUMIF(reprange,repkey,unitrange)),OFFSET(repkey,0,1))))
The outwards MAX gets the maximum value
from an array that should have a number of
FALSEs and one (or more, if tied) positive
value. This works if at least one rep
has a positive number of units.
HTH
