Max finds and returns the maximum value in a range, but it doesn't tell you whether there is more than one maximum value. This function returns the address(es) of the maximum value(s) in a range, as shown in Figure 4.17. ReturnMaxs(Rng)

The argument is

Rng—The range to search for the maximum value(s). Function example:

Function ReturnMaxs(Rng As Range) As String Dim Mx As Double Dim myCell As Range

'if there is only one cell in the range, then exit

If Rng.Count = 1 Then ReturnMaxs = Rng.Address(False, False): Exit Function Mx = Application.Max(Rng) 'uses Excel's Max to find the max in the range 'Because you now know what the max value is,

0 0

Post a comment