Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Begin and End Dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Begin and End Dates
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. -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Begin and End Dates
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" wrote in message ... 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. -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Begin and End Dates
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 -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Begin and End Dates
Funny how much joy you can get from a tiny bit of code Finally working.
Thank you K Dales "K Dales" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where to begin??? | Excel Worksheet Functions | |||
Sum Begin/End selectivity | Excel Worksheet Functions | |||
How do I begin value with the number zero? | Excel Discussion (Misc queries) | |||
x-axis time series with 2 different begin dates | Charts and Charting in Excel | |||
how do i begin to use excel? | New Users to Excel |