identify earliest & latest date groups in table

Try this:-
Results start sheet (2) "A1".

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

Thanks for offering a solution to this.

The code almost works but for some reason the dates are in different formats and or transposed and don't seem to want to re-format.

Eg 01/08/11 is being returned as 08/01/11 and some dates are shown as dd/mm/yy and others dd/mm/yyyy.

Please would you take another look at it?

Thanks,

Louise

Try this:-
If this does not work try formatting the columns where the dates will be returned.

Please Login or Register  to view this content.

Regards Mick

Hi again Mick,

This time I get a run time error 13 - type mismatch error.

I've tried formatting the columns but makes no difference.

It must be almost there, please would you suggest a fix?

Thanks

Louise

Have you got any cells in those 4 columns that are blank or cells that should be a date, and are not.

The destination worksheet and cells are blank.

Thanks

Louise

Sorry, what I meant was, are any of the Data cells That should have Text Numbers or Dates Blank or any of the Data cells that should contain Dates, Blank.
Example :- Your data is in Columns "L to O", if say there are 100 rows in those columns with data if any cell within this range is blank or has not got a date, you could get that error.
If there are blanks and that is how your data would normally be presented to you, then I can perhaps alter the code to take account of that, but I was trying to understand where the error came from.
Mick

Sorry I misunderstood.

The data in columns L & M is alphanumeric for grouping. The data in N & O is date format, can be over 1000 rows but there are occasional blanks.

Thanks

Louise

I forgot to say - also row 1 contains text headings.

Louise

Hi Mick,

I get a Compile error - expected End With

This sounds simple but not sure where to put it?

Thanks

Louise

Sorry , Bad Copying.
I've altered the code above
"End With" is right at the bottom just above "End Sub"
Mick

Run time error 13 - type mismatch

the debugger is highlighting this line;

Please Login or Register  to view this content.

I get the feeling we're nearly there

Thanks for your patience

Steve

Is it possible to post your file with the data.
Mick

Hi Mick,

Here it is

Thanks

Louise

Have you tried running the code on the File you sent me , because this was the Result.
Which looks about how I expected it:- Your thoughts !!!

Please Login or Register  to view this content.

Regards Mick

What you have obtained is exactly what I'm after but I'm still getting the error. Is it possibly anything to do with where the macro is kept. At the moment it's in a module (module 1).

Thanks,

Louise

I've alreadry tried that, but I was thinking when you run the code you should have the sheet with the Data as the ActiveSheet (Open) , NOT the results sheet, (sheet2). This will give you the error "Type Mismatch".
Mick

Hi,

I've tried running the macro from the sheet with the source data in it and got a different error message. Does this give any clue?

run time error 9 - subscript out of range

Given your success with it we must be so close

Thanks for continuing to help with this.

Louise

Have a look at your Returned file.
There is a Command Button (Ref "H1" of Data sheet) that will run the code .
also there is a Double click Event. Both where working.
If you Double Click "L1" in Data sheet, or Click the Button Sheet (2) should be updated.
Regards Mick

Hi Mick,

I don't know why my version isn't working but I've copied all of my data into the workbook you've just returned and it works perfectly.

Maybe I'll just operate it this way.

I really do appreciate the time and effort.

Thanks,

Louise

Hi,

Can Mick or someone help with this almost fantastic code please?

I thought we'd got there but not quite.

It is taking the data in KC Acquired Dates and identifying the earliest and latest dates in each combination of work order & task id.

The code works great but the dates are getting jumbled up.

This way beyond my understanding and I'd appreciate your help.

Thanks,

Louise