how to create macro to use cell references for data filter criteri

[Guest]

I have a large series of data where the criteria for the data filter updates
a linked chart. The data looks as follows:

Time Stamp Data1 Data2 Data3 Data4
6/21/07 10:05AM 123 124 125 126
6/21/07 10:10AM 223 224 225 226
6/21/07 10:15AM 323 324 325 326
etc.

I use the data filter to select the criteria that is greater than or equal
to first specified date & time and is less than or equal to second specified
date & time. Because the chart is on a different sheet and there are other
input fields that are also required for the chart, I’m trying to automate as
much of the charting as possible.

I have two cells where I would input the start and end dates. How do you
create a macro that will take the start date as the first criteria in the
data filter and the end date as the second criteria? The start and end dates
will vary. When I’ve tried to create the macro, it will take the input start
date as the actual date, meaning, if I change the start date, I will still
get the original start date. Is there any way to use whatever I type in for
the start and end dates for the data filter criteria?

On a similar topic, since the chart updates to the parameters of the data
filter, the y-axis scale becomes skewed. I’ve created a formula to calculate
the min and max for the scale. Again the macro I’ve tried to create only
recognizes the actual value in the cell rather than the formula output. How
would I create a macro to recognize the result of the formula?

Thank you in advance.

 

[Debra Dalgleish]

You could create a criteria range, with two columns with the same
heading as the date field in the data table. Use formulas to pull the
dates from the start and end date cells, e.g.:

Date Date
=">=" & StartDate ="<=" &EndDate

Use this criteria range when you run the Advanced Filter.

 

[Guest]

hi debra,

thanks for your response. i don't quite understand (i'm a bit of a macro
novice). the data is stored in excel. in a different workbook, i have in
two columns and rows:

A B
1 start date end date
2 [input date] [input date]

now i want to create a macro that recognizes the date i input for start date
as the "greater than or equal to" criteria in the autofilter, and the date i
input for end date as the "less than or equal to" criteria in the autofilter.
the input dates will vary.

so should i record a macro using the input dates and then use VB to modify
the criteria to =">=" & StartDate ="<=" &EndDate?

does this work for scaling the chart to your min and max ranges?

thanks for your help!!

 

[Guest]

aha! i got it now. thank you!

You could create a criteria range, with two columns with the same
heading as the date field in the data table. Use formulas to pull the
dates from the start and end date cells, e.g.:

Date Date
=">=" & StartDate ="<=" &EndDate

Use this criteria range when you run the Advanced Filter.