Begin and End Dates

[Joe Blow]

I have tried this from multiple angles but have yet to make it work.

I have a database of unknown size (between 3 and 30000+ entries) that are
sorted (or they have become sorted) by date.
I only want to put the relevant dates into the Pivot table.
I want the user to enter the dates in a MsgBox type thing because they can
be assured of putting them in the wrong cell.
The macro has to work without user input (except for the dates) as I can not
be there with them.

I was trying to just step through the whole thing and flag the rows that
enter/exit the defined date range but I can't seem to be able to relate the
MsgBox date (date1 and date2) to the dates in the A column. I can't get the
autofilter to understand my date1 and date2 (As Date) either. Also the
advanced filter doesn't seem to work for me( everything I have read about it
doesn't seem to relate to the advancedFilter I have. Excel 2002)

A B C
01/14/2004 XXX YYY
01/15/2004 XXX YYY

 

[K Dales]

Various factors might be at work here but the most likely
suspect is how you are storing the dates. The first thing
to check is the format of the column of cells containing
your dates: are these truly date values, or could they be
text? If they are text you will need to convert them to
true date values to make everything work.

Assuming you did take care of this, the next potential
problem is how you are referring to your dates in your VBA
code. Be sure to put everything into Date type variables
before you do any comparisons or filtering. Your
MessageBoxes are returning text so are you sure you are
converting the values properly to dates? I like using the
DateValue function for this purpose since it is easy to
use and mistakes can be easily seen and debugged.

As for AdvancedFilter, that is a pretty big topic and hard
to get into it without more specifics of what you want to
know. I don't think I would use Autofilter here, though:
I think I would just find the proper rows in the specified
date range and set that up as the source data range for
the PivotTable.

Hope this helps.

 

[Joe Blow]

That does help. If you could give me a code line that would convert a cell
to MM/DD/YY(YY) format that would be great. I am currently using:

Dim date1 As Date
date1 = InputBox("Enter the begining date:")

If this doesn't fit please show what will, and for a bonus, Why it doesn't
work.
Thank you very much.

 

[K Dales]

InputBox technically returns a String result - your code
is setting a Date variable equal to this String value.
You are trusting VBA to take care of the conversion for
you, but it would be better to explicitly convert the
value.

To convert from String to Date: First, you need somehow
to check the user input to make sure it is a String that
is recognizable as a date. The IsDate function will let
you know if this is so. Then you can use DateValue to
explicitly tell VBA to turn the String into a date value
to use for comparison purposes:

Dim StrDate as String, DtDate as Date

StrDate = InputBox("Enter the beginning date:")

While Not(IsDate(StrDate))
StrDate = InputBox("Date entered not valid; Re-enter
beginning date:")
Wend

DtDate = DateValue(StrDate)

Now DtDate contains a Date value that can be used for
comparison and calculations. Just remember to also check
that the Excel cells containing your dates are also
formatted as "Date" and not as "General" or "Text" to make
sure that VBA will interpret them correctly as Date values
also.
K Dales

 

[Joe Blow]

Funny how much joy you can get from a tiny bit of code Finally working.

Thank you K Dales