Macro to sort?

Hi,
I would like to know if it is possible to find a macro which can sort under certain conditions? In fact, I have on one sheet two cells where the persons can enter 2 dates (a beginning date and an end date) and once filled, this macro could sort a chart present in another sheet in function of these dates.

Is this possible?

I have attached my work to make it more understandable!


Thanks

Originally Posted by koda86

Hi,
I would like to know if it is possible to find a macro which can sort under certain conditions? In fact, I have on one sheet two cells where the persons can enter 2 dates (a beginning date and an end date) and once filled, this macro could sort a chart present in another sheet in function of these dates.

Is this possible?

I have attached my work to make it more understandable!


Thanks

Hi,

As the chart already has buttons to sort either column ascending or descending it is difficult to imagine what your button would do.

Would your button request which date to sort, would it request whether to sort ascending etc, is there any advantage to this button that makes it worth your effort?

The code for such a button, record a macro, and sort the chart all 4 ways, then edit the macro.

Does this help you?
---

Originally Posted by koda86

Hi Bryan,
In fact this button would allow the persons to sort directly the data between these two dates, it is really useful for me to have this macro. I tried to record a macro and change something but I did not succeed in doing that.

Range("A4:O1000").Sort Key1:=Range("F5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This is the macro which sorts my dates ascending but I can't really understand how to find the macro which sorts the column F only between the two dates selected in the other sheet.


Do you know how to do it?


thanks

Hi,

why would you sort ONLY column F ? - this would 'disjoint' your data

Am I missing some point?
---

Originally Posted by koda86

Does someone know if there is any way to change the formula of a sort and put criteria in: for example, sort this chart so that I can see only the dates before 01/01/06? This is the formula which sort ascending my column F which is the one of the dates so I would like to know how I can sort this column to the criteria 01/01/20006?


Range("A4:O1000").Sort Key1:=Range("F5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Thanks

HI,

No, but you have two other options.

Either setup a separate chart area and use pointers to which data from your sorted list is moved to the chart-data-area,

or

with your data sorted as required, locate the first cell, and use this to modify the charts 'Series Source Data Values' (record macro and adjust this to see the code).

hth
---

Well, three options if you want to consider Filter the data (on dates) into a separate chart data area.
---

Originally Posted by koda86

Hi, Bryan,
thank you for your solutions, I don't really know which one is the simplest but for me maybe the final one (Filter the data into another chart data area) seems to meet perfectly my needs. I don't know how to do it so maybe you can help me to do it. I have attached an excel folder with my chart and with the cells where the persons will enter a beginning date and an end date. My final objective , in fact, is to filter this chart with only the lines whose the end date (Column E) is between this two dates.

I hope you will understand what is my aim!

Thank for your help again!!!

This can be done on the button, but you do realise that the chart, rather the Filtered Table already has this feature built-in? - drop down the arrow, Custom, greater than or equals, select a date, less than or equals, select a date.

hth
---

Originally Posted by koda86

Hi Bryan,
Thank you for this tip because I did not know that we could do that!!! I have tried to apply this filter to the related cell where the persons will enter the dates, to make something automatic bit it did not work:


Selection.AutoFilter Field:=6, Criteria1:=">='Research per date'!K20"

Maybe you can help me again??

Field 5 ?

Selection.AutoFilter Field:=5, Criteria1:=">='Research per date'!E6", Operator:=xlAnd _
, Criteria2="<='Research per date'!E9"

===

Originally Posted by koda86

Thank you for your answer but the macro does not work, there is an error and I don t understand why??? because your formula seems good but when I try to run it, it does not work.


Is it possible to solve that?

Thanks again for your help

as there is already the built-in - is there any reason to do the button? - if so I will look again tomorrow,

---

Originally Posted by koda86

Sorry Bryan, I know I insist on this problem but this is the key of a big project for me and if someone can solve this problem, I have almost finished this task. And, this button can be really useful for me!!
Again Thank you very much for your patience!!!!!!!!!!!!!!! You have already helped me a lot in doing that!!!


Thanks

ok - it should just be a matter of Record a macro, make the selection, and transfer those instructioins to your button code, except that, a filter must utilize one of the known dates in it's list.

more tomorrow
---