Need Help with Application.Max

[ChrisBEE1]

Hi all,

I need help with how to use Application.Max. I have a spreadsheet with three columns of data Months, Date, and Sales. Sales are recorded daily and what I'm trying to accomplish is to establish the date of the maximum sale and have that value highlighted in the spreadsheet using vba.

Any suggestion on how to implement this would be greatly appreciated.

Thanks,
Chris

 

[RobBroekhuis]

When I've had to do something similar I've used
application.worksheetfunction.max() to find the maximum value, and then
application.worksheetfunction.vlookup() to find that maximum value, and find the corresponding date (or other field) in a different column. This only works if the date column comes AFTER the value column. And you actually don't need VBA to do this, it works just fine using worksheet functions. Of course, you could also just loop through your entire table and keep a running maximum, along with the corresponding date, something like

iRow=1
dmax=0
do while cells(iRow,1)<>&quot;&quot;
if cells(iRow,2)>dmax then
dmax=cells(iRow,2)
datemax=cells(iRow,1)
end if
iRow=iRow+1
loop

where your date would be in column A, your value in column B.
Rob

 

[DaleWatson123321]

Chris,

Another option... allow me to demonstrate the power of Excel's &quot;database extraction&quot; option - where you'll be able to &quot;click-a-button&quot; to extract and isolate the record with Maximum Sale - for whatever period you prefer.

The orginal data is left as is, and a copy of the data is extracted to a separate sheet, based of whatever &quot;criteria&quot; you want to specify. You can easily generate Maximum totals per month, and/or extract the Maximum record for each month, or whatever else makes sense.

You indicate that your database contains a field for Month, in addition to Date. Using the database functions, it's possible to generate Monthly Reports and/or Yearly reports based ONLY on the Date field. You will therefore likely be able to eliminate the Month field.

Without seeing the power of Excel's database functions, you're missing out on the use of one of Excel's most powerful utilities.

If you're interested, email me a copy of your file, and I'll modify it to demonstrate what I'm referring to. If you happen to have sensitive data, replace it with fictitious data that still reflects the type of data you're working with.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca