VB code needed for sorting function - please help!

[monica S]

Hi,

This has been driving me crazy... hopefully someone out there can help.

If I have an ORIGINAL range of dates in cells A1:A5:

22-Apr-04
30-Apr-04
22-May-04
22-Aug-04
22-Sep-04

and a TARGET date of say 16-Jun-04 in cell B1.

I would like to have a FUNCTION that will 'read' the OriginalDates, take the TargetDate into account, and then just INCLUDE or INSERT the target date among this original range of dates. The function would return the new dates in ascending order (i.e. with dates closest to now coming first and dates furthest from now coming last). The function would have to be typed in as an ARRAY function (with the { } bracketing the formula and closing it using Ctrl+Shift+Enter) and would return 1 more row than what is passed through OriginalDates argument.

so, the function would have two arguments and go something like this:

{=IncludeDate(OriginalDates,TargetDate)}

or;

{=(A1:A5,B1)}

In our example, the function would return the following sorted set of dates (with the June 16, 2004 date included and SORTED among the original set of dates):

22-Apr-04
30-Apr-04
22-May-04
*16-Jun-04*
22-Aug-04
22-Sep-04

in cells C1:C6 as a NEW OUTPUT RANGE of dates.

In this case, we would copy our function down 6 rows (one more than the OriginalDates) and close it with the Ctrl+Shift+Enter to make it an ARRAY of 6 rows.

A MACRO WILL NOT HELP IN THIS CASE. ALSO, USING THE DATA --> SORTING FUNCTIONALITY WILL NOT HELP IN THIS CASE. TRUE, BOTH WILL WORK, BUT THIS PROJECT CALLS FOR A **FUNCTION** TO HANDLE THE TASK.

Any ideas on how to go about doing this?

Thank you very much!
~ Monica

 

[NateO]

Whatever. The mistake was was noted. And not only is the warning offered, I've posted workaround in this thread.

As for ongoing, I'm at a loss as to what you're talking about.

 

[Legacy 21301]

Whatever. The mistake was was noted. And not only is the warning offered, I've posted workaround in this thread.

As for ongoing, I'm at a loss as to what you're talking about.

"As for ongoing" ?
I'm at a loss as to what you are trying to convey by this phrase.

The point I was trying to make in my last thread was that the behaviour was due to a Filter and not to SpecialCells - to some readers this may not have been entirely clear from your posts.

 

[NateO]

I agree that it's not the Special Cells Method causing the behaviour in question.

When there are one or more rows hidden by a filter, it is only possible to delete entire row(s).
This is true whether via SpecialCells, a cell by cell loop, manually without VBA, or whatever.
This, of course, is entirely logical.

The final assertion here could be up for debate.

 

[monica S]

It works.... Thank you!

 

[Legacy 21301]

.

When there are one or more rows hidden by a filter, it is only possible to delete entire row(s).
This is true whether via SpecialCells, a cell by cell loop, manually without VBA, or whatever.
This, of course, is entirely logical.

The final assertion here could be up for debate.

So why don't you?

 

[NateO]

I believe that I did.

Filtering by rows makes a fair amount of sense. In Excel, being forced to delete by row does not as much, irrespective of how you got there. It's following the norms of DB technology versus spreadsheet technology.

I don't see the logic in saying that because you filter by records, you cannot delete by fields in a spreadsheet. If it's good for hidden rows, why not filtering? Probably more of a limitation of the Application's development than it making precise logical sense or desirability.

 

[Legacy 21301]

In view of your opinion that it "makes a fair amount of sense" to be able to filter only by complete rows and not within an individual column, what are your reasons for thinking that the deletion of individual cells in a column should be allowed in a filtered list rather than just being able to delete complete rows?

Does it not also make "a fair amount of sense" to be able to delete only complete rows?

Perhaps it would make more sense if it were possible to filter within one column only !

 

[NateO]

I could see merit to that enhancement! Flexibility is a good thing with Spreadsheet technology, n'est pas.