filter by dates

[ndendrinos]

By inputing on the top text box of my form the code filters by name and sorts by date.

What I need is to further filter this time by inputing on the form a "from date" and a "to (inclusive) date" thus producing a "custom" statement for my customer.
Thank you

Edit:The "further filtering" could be done by deleting the rows that are not within the range of the "from & to" dates

[pike]

ndendrinos
try..adjusting the date formats
Ive got it in day moth year


Private Sub CommandButton1_Click()
Dim rng As Range, a$, z$, aDay$, zDay$, aMonth$, zMonth$, aYear$, zYear$, aDate$, zDate$, StartDate$, EndDate$
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If TextBox2.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox2.Value
rng.AutoFilter

Columns("B:B").Select
Range("A1:F17").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
a = TextBox3.Value
z = TextBox4.Value
aDay = Day(a)
zDay = Day(z)
aMonth = Month(a)
zMonth = Month(z)
aYear = Year(a)
zYear = Year(z)
aDate = DateSerial(aYear, aMonth, aDay)
zDate = DateSerial(zYear, zMonth, zDay)
StartDate = aDate
EndDate = zDate
Range("B1").AutoFilter Field:=2, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate



'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub

[ndendrinos]

Hello Pike and thanks for answering my question.
Sorry to say that I cannot get the code going.
Changed the format of the dates in my sheet to dd/mm/yyyy to no avail.
Also tried to change the date "format" in your code and that too failed.
Both times the code executes but the result is the same ... just the first selection shows (the start date)

I think the problem is with this line

Range("B1").AutoFilter Field:=2, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

[mdmackillop]

Your date columns are 7 figure numbers formatted to look like dates. These need to be true date values.

[ndendrinos]

Hello mdmackillop tried to change the format to "date"
this did not work.
Changed to text and that did not work either.

[mdmackillop]

Format as date then enter proper values. The numbers in the cells will not convert to dates. 9052009 will occur in 247,000 years

[ndendrinos]

Got it
I've formated to date *14/03/2001 and that fails
14/03/2001 works.
Always used the first so I guess the asterix means something ... but what?
Apologies to Pike are due.
Thank you mdmackillop

[ndendrinos]

This is not working ... it did work just once.
Maybe I misunderstand mdmckillop's "Format as date then enter proper values"

If I format the whole of B column both in sheet Vault & Filterd to "date" 14/03/2001 and type like this: 16/09/2009
should this work? with me it does not

[pike]

its a bit of a work round with your custom number format
but try...

Private Sub CommandButton1_Click()
Dim rng As Range, StartDate, EndDate, zdate, adate
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If TextBox2.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox2.Value
rng.AutoFilter

Columns("B:B").Select
Range("A1:F17").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With Sheets("Filtered").UsedRange
.Columns(2).Insert
.Columns(2).NumberFormat = "General"
.Columns(2).FormulaR1C1 = "=RC[1]"
End With
ActiveSheet.AutoFilterMode = False
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
adate = UserForm1.TextBox3.Value
StartDate = Replace(adate, "/", "")
zdate = UserForm1.TextBox4.Value
EndDate = Replace(zdate, "/", "")
ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
' Exit Sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub

[ndendrinos]

Hello pike and thank you for all the good work.
The code works great but creates an extra column in sheet "Filtered"
see "filter pike"
Not sure how to fix this but I could add to the code to delete the extra column once the filtering is done.
Thanks again

[mdmackillop]

Submit your book with real dates and I'll have a look.

[ndendrinos]

With pleasure mdmckillop ... the"with real dates" I do not understand still ... I know you explained that "00"/"00"/"0000 mean nothing to excel in this contest but I do like the functionality of typing a date as 09012009 and get 09/01/2009.


This sample is what I have come up with untill such time as I get a revised copy from pike.
It also is a compromise but it works.
I type the dates in the form as: 09012009

The sheet "statement" will look different when done with it.
Too bad I cannot hide sheet "Filtered" without adding to the code.
Many thanks

[ndendrinos]

Hope I catch you on time
Lots of new developments overnight and I would please ask you to follow this link and perhaps participate as well.
The file is the same as the one shown here.

http://www.vbaexpress.com/forum/showthread.php?t=28468

Regards, Nick
P.S. Lucas has contributed ... xld too although he must be nauseated by the number of "selects" I've come up with to put this infernal project to rest.

[Bob Phillips]

I also notice in that workbook that you are not using real dates, but numbers like 9132009 for 13th Sep. Why? As well as being counterintuitive, it stops Excel from handling dates internationally (we in Europe would use 13/09/2009 not 09/13/2009).

[mdmackillop]

You cannot expect Excel to filter on "Dates" which are not real Dates. If you want to type in 01022009 then this can be done, using a Change Event.
Set your date columns to General format, add this to the worksheet modules, then reenter your dates as before.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
Target = DateValue(Format(Target, "00/00/0000"))
Target.NumberFormat = "dd/mm/yyyy"
Exits:
Application.EnableEvents = True
End Sub

[pike]

Hi ndendrious which is the latest work?

[ndendrinos]

goodmorning to all
pike: I attach the file here "Filter and Statement"

xld/mdmckillop:
in this attachment I have typed the dates a la European ...
then searched Max's Kennel for activity between 01092009 and 03092009 (included) . The results are as good as when using dates in the US format.

I have no doubt that all of you are right when you suggest I'm wrong but I would like to remind you of the objective of my posting here and the problem with Ann Troy's code when applied to this particular situation.

Each row in "Vault" represents all the data that is initially typed when I prepare an invoice to my customer.

This data spans from column A to column CH

I can reproduce any invoice by "inv No" or the customer's PO " importing the data from "Vault" on a "Invoice Template"

The last step in the project was to have the ability to produce a custom tailored "statement"

All that I wanted to show on the statement was the content of columns: A,B,C,D,F,CE,CF,CG,and CH.

I submit to you very humbly that is that my sample does indeed do all that's required of it.

Flaws in the code writing are many, in this you are all right. Were I respectfully differ from xld's interpretation of what is :

counterintuitive, it stops Excel from handling dates internationally (we in Europe would use 13/09/2009 not 09/13/2009).

I find it easier to type a date as 01122009 than 01/12/2009. The sample also proves that the date format US vs. EEU is irrelevant , again, in this context.

I hope I do not come across as biting the hands that feed me .

I will please ask you all to use the sample I post here and judge the results and not the content of the VBA.

Then try using it by using Ann Troy's code, xld's, or pikes
you will then agree perhaps that they create problems in this file while I'm sure they are perfect as a single entity.

Sorry about the long tirade ( I hope tirade is an English word as well)

Note: it appears there is a problem attaching the file for now ... I will attach it in a few minutes.

[mdmackillop]

I can see some logic in entering a date as 20091231 (yyyymmdd), in that it can be ordered and easily searched, and I have used it manner in file naming for that reason. Post #15 shows that you can get real dates from your data entry method, but I would never store dates as you suggest.

[ndendrinos]

Point well taken.
I will then go back to the drawing board , do it all over again and post a revised sample.
Thank you mdmackillop

[ndendrinos]

Started on the wrong foot ... the input of 09012009 to become 09/01/2009 (mm/dd/yyyy) is not working with a sheet change event.
Maybe the problem is excel changes the format from "general" to "date" on input of a date in the 09012009 style or 09/01/2009.
I attach an example

[mdmackillop]

Finally got back to this one!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Formula = "" Then Exit Sub
If IsDate(Target.Formula) Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
Target = DateValue(Format(Target.Formula, "00/00/0000"))
Exits:
Application.EnableEvents = True
End Sub

[ndendrinos]

Thank you I was beginning to feel "persona non grata"
Your code works but a la European.
I type 09152009 (Sep 15) & get 15/09/2009 & the format of the cell changes to Date *14/03/2001.

If I change the code to :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Formula = "" Then Exit Sub
If IsDate(Target.Formula) Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
Target = DateValue(Format(Target.Formula, "00" / "00" / "0000"))
Exits:
Application.EnableEvents = True
End Sub then the result for 09152009 IS 09/15/2009 BUT
the format has stayed "general" I will have to test to see if the code will filter properly with dates in 2008 along with dates 2009

You were right when you said that Excel could not filter on dates that are not dates ... I discovered this the hard way this morning when I merged data from 2009 along with data from 2009
Strange how all is OK if I filter data dated the same year.
Thank you mdmackillop

[ndendrinos]

Well my change to your code does not work as expected.
If this is unsurmountable then I will start typing like this 09/15/2009 and set the format to "date" *14/03/2001
I will test this now

Edited: This does not work either

[mdmackillop]

Try this variation. Swap Day/Month to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Formula = "" Then Exit Sub
If IsDate(Target.Formula) Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
dt = Format(Target.Formula, "00000000")
dy = Left(dt, 2)
mth = Mid(dt, 3, 2)
yr = Right(dt, 4)
Target = DateSerial(yr, mth, dy)

Exits:
Application.EnableEvents = True
End Sub

[ndendrinos]

I'm taking way too much of your time ... tested all possible scenarios and the only one that works requires me typing 09/16/2009 and get same if the Format=*14/03/2001.
This will not require any sheet code.

I hate to quit but I have to face reality.
In a couplr of days I'll start all over again and hope to do better.
Meanwhile I leave the post "open" just in case a solution is found

Thank you again, Nick

[ndendrinos]

this was posted on mrexcel http://www.mrexcel.com/forum/showthread.php?t=418597

and might work for me if I could change the code to accept and filter typing the dates on the sheet as well as the form as: 09/25/2009 (with format: March 14,2001

Here is the file, the calendar is not perfect and the author's attempt to have the user type the dates as 09/25/2009 in the filter form fails to do so... see notes on the sheet.
Also I need help in deleting from the code the functionality of the time ... just need the dates
Thank you

[ndendrinos]

Started the cleanup and got rid of the calendar as well.