Finout max value against given date

[tqm1]

Dear Experts

In my Worksheet ("Date") I have following two columns as

Code-----Date---
--1----01/05/07
--3----01/05/07

--5----15/05/07
--6----15/05/07
--7----15/05/07

--4----03/05/07
--1----05/05/07
--5----04/05/07
--4----03/05/07

How to findout Maximum Code in column A against Date=15/05/07
The answer is =7

Note: Date format is ("dd-mm-yy") 'British date

Please help

[sweep]

Hi,

Use an array formula like so,

=MAX(IF(date_range=date,data_range,"")) [confirm with CTRL, SHIFT & ENTER]

It may be wise to use a cell reference to reference the date to ensure you're checking for the right this, viz

eg

=MAX(IF(B1:B8=D1,A1:A8,""))

[tqm1]

Dear Sir,

Your followong array formula work fine within a cell
{=MAX(IF(B1:B8=D1,A1:A8,""))}

But when I use it under command button as follows

Please Login or Register  to view this content.

then it show error message
Run-Time Error 9
Subscript out of range

Please modify, how to use with command button

[sweep]

I think you'll have to either, perform the array loop in your VBA code, or apply the .FormulaArray method

Dave

[Simon Lloyd]

tqm1, please read these rules!, you have had assistance with your problem and still saw fit to cross post to another forum without in forming either them or the people that have been kind enough to help you here!

Cross posted here

This thread is now locked, read the rules if you agree to abide by them send a PM to one of the moderators of this site and your thread will be made active again