Please help with application.max

ChrisBEE1 · Feb 11th 2003

:puzzled:

Hi all,

Can someone help me with the vba code to find the maximum value in a column. My spreadsheet incorporates several columns Agent Number, Date, and Sales are the main concerns. I want an automatic procedure where my macro hightlights the maximum sales of each agent. How can adapt the following macro to suit my needs.

******************************
code:

Sub Find_Max()
Dim searchRange As Range
Dim cell As Range
Dim maxValue, maxRow As Integer
Set searchRange = Columns("L")
maxValue = Application.Max(searchRange)
maxRow = Application.Match(maxValue, searchRange, 0)
Set cell = Cells(maxRow,12)
cell.Select
End Sub
*********************************

Any help on this would greatly appreciated.

Thanks,
Chris

btadams · Feb 11th 2003

Have you tried replacing it with

Application.WorksheetFunction.Max(searchRange)

Denis · Feb 11th 2003

I'm not sure what you've got the problem with. Is it just getting the cell color to change? If so, then I merely tweaked your declarations slightly and added a line at the end to color the cell my favorite shade of yellow.

Code

Sub Find_Max()
  Dim searchRange As Range
  Dim cell As Range
  Dim maxValue As Integer
  Dim maxRow As Long
  
  Set searchRange = Columns("L")
  maxValue = Application.Max(searchRange)
  maxRow = Application.Match(maxValue, searchRange, 0)
  Set cell = Cells(maxRow, 12)
  cell.Interior.ColorIndex = 19
End Sub

Display More

However, if you're problem is looping through the sheets, then please repost.

ChrisBEE1 · Feb 11th 2003

Hi bt,

No I have not, according to Dave Hawley in "MS Excel Visual Basic Examples" that syntax can return errors. He suggest dropping the 'Worksheets'.

Actually the code that I displayed works fines, I simply need it to do more. In it's present state it highlights "the maximum value" in the column. I need it to hightlight each maximum sale value for each Agent on a monthly basis.

Thanks,
Chris:puzzled:

ChrisBEE1 · Feb 11th 2003

Hi Denis,

The code I dsplayed works fine. I need help modifying it to yield the following results: agents 1 thru agents 100 are all in column 9, the date of sale is in column 10, and the amount sold is in column 12. I want to highlight all the maximum sales values for each agent. Whereas, the code that I displayed is highlighting one singular maximum value.

Looping will be required, but how do I start?

Again, any help at all will be greatly appreciated.

Thanks,
Chris

Denis · Feb 12th 2003

I've knocked up some code that really isn't the prettiest thing in the world or the fastest, but it does what you ask for. However, I have assumed that you have not used "Autofilter" on this sheet. If you have, do not run this code.

Here is the code:

Code

Public Sub Find_Max()
'turn on Autofilter
  Dim objRange As Range
  Dim objMaxRange As Range
  Dim i As Integer
  
  Application.ScreenUpdating = False
  On Error Resume Next
  'begin looping through agents.
  For i = 1 To 100
    ActiveSheet.Range("I:L").AutoFilter Field:=1, Criteria1:=i
    Set objRange = ActiveSheet.AutoFilter.Range
    Set objMaxRange = ActiveSheet.Range("I:L").Find(WorksheetFunction.Max(objRange.SpecialCells(xlCellTypeVisible)))
    Range(objMaxRange.Offset(0, -3), objMaxRange).Interior.ColorIndex = 19
  Next
  
  ActiveSheet.Range("I:L").AutoFilter
  Application.ScreenUpdating = True
End Sub

Display More

Insomniac · Feb 12th 2003

Just an observation, but it appears that setting conditional formatting of columns I-L to

Formula is =$L1=MAX($L:$L)
(format pattern to yellow)

would achieve the same result.

Denis · Feb 12th 2003

I don't think it would achieve the same result. Wouldn't that just format the MAX value in column L?

Insomniac · Feb 12th 2003

I tested it and it will format any column with this conditional formatting.

The $L in the formula means it will check the condition of the cell in row L no matter what column the formatting is in.

Will post a sample if you want!

Denis · Feb 12th 2003

Please do, I'm not that good with Conditional Formatting.:)

Insomniac · Feb 12th 2003

Sample Here

Denis · Feb 12th 2003

It doesn't do the same thing. That's what I thought. It does what the OP is trying to correct.

There is more than one agent whereas in your spreadsheet "blaa" is the only agent. In the example I've attached, I've added the agent "Denis" with a value of 200.

What the OP wants to highlight the max for "blaa" and the max for "Denis". Not just the max in the column.

Insomniac · Feb 12th 2003

Ok I see your point, I was under the impression that it was only reqiured to highlight the row with the absolute maximum value in row L.

This will only work if the values for each agent are in a separate column.

I stand corrected!!!!!

Please help with application.max

Participate now!

Share