Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
I have used a Input Form which collects Date Range. In execute event I have
written following code Selection.AutoFilter Field:=20, Criteria1:="=" & Txt_From, Operator:=xlAnd, Criteria2:="<=" & Txt_To I dont find any mistake in the above code. Data is entering in the custom filter. But filter is not doing its work. Further Date format which I Enter is DD/MM/YY. I have applied filter on several other fileds and it works. But filter on Date fields doesnot work. Please send your valuable solution. Regards Grisha Jose |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
Try converting the dates to serial numbers. For example:
Selection.AutoFilter Field:=20, _ Criteria1:="=" & CLng(Txt_From), _ Operator:=xlAnd, Criteria2:="<=" & CLng(Txt_To) Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date") strEnd = Application.InputBox("End Date") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) Grisha Jose wrote: I have used a Input Form which collects Date Range. In execute event I have written following code Selection.AutoFilter Field:=20, Criteria1:="=" & Txt_From, Operator:=xlAnd, Criteria2:="<=" & Txt_To I dont find any mistake in the above code. Data is entering in the custom filter. But filter is not doing its work. Further Date format which I Enter is DD/MM/YY. I have applied filter on several other fileds and it works. But filter on Date fields doesnot work. Please send your valuable solution. Regards Grisha Jose -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
Debra Dalgleish wrote in message ...
Try converting the dates to serial numbers. For example: Selection.AutoFilter Field:=20, _ Criteria1:="=" & CLng(Txt_From), _ Operator:=xlAnd, Criteria2:="<=" & CLng(Txt_To) Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date") strEnd = Application.InputBox("End Date") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) Grisha Jose wrote: I have used a Input Form which collects Date Range. In execute event I have written following code Selection.AutoFilter Field:=20, Criteria1:="=" & Txt_From, Operator:=xlAnd, Criteria2:="<=" & Txt_To I dont find any mistake in the above code. Data is entering in the custom filter. But filter is not doing its work. Further Date format which I Enter is DD/MM/YY. I have applied filter on several other fileds and it works. But filter on Date fields doesnot work. Please send your valuable solution. Regards Grisha Jose Hi: I have written an Auto filter program in VBA for a work sheet for name of people and their countries, which is controlled by button click, and is running quite successfully. However I would like to have the results of the program to be shown / thrown on separate sheet rather than the same database sheet, and would like to print those result. Will any one help me here? . Thanks – Raj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter
The following code will copy the filtered range to Sheet2. You can
record the steps as you print the sheet, and copy the resulting code to your macro: '============================= Sub CopyFilter() 'copy data and headings from filtered table Dim rng As Range Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") On Error Resume Next Set rng = ws1.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then MsgBox "No data to copy" Else ws2.Cells.Clear rng.Copy Destination:=ws2.Range("A1") End If End Sub '============================ Rajendra Talele wrote: Debra Dalgleish wrote in message ... Try converting the dates to serial numbers. For example: Selection.AutoFilter Field:=20, _ Criteria1:="=" & CLng(Txt_From), _ Operator:=xlAnd, Criteria2:="<=" & CLng(Txt_To) Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date") strEnd = Application.InputBox("End Date") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) Grisha Jose wrote: I have used a Input Form which collects Date Range. In execute event I have written following code Selection.AutoFilter Field:=20, Criteria1:="=" & Txt_From, Operator:=xlAnd, Criteria2:="<=" & Txt_To I dont find any mistake in the above code. Data is entering in the custom filter. But filter is not doing its work. Further Date format which I Enter is DD/MM/YY. I have applied filter on several other fileds and it works. But filter on Date fields doesnot work. Please send your valuable solution. Regards Grisha Jose Hi: I have written an Auto filter program in VBA for a work sheet for name of people and their countries, which is controlled by button click, and is running quite successfully. However I would like to have the results of the program to be shown / thrown on separate sheet rather than the same database sheet, and would like to print those result. Will any one help me here? . Thanks – Raj -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel auto-filter does not filter certain columns | Excel Discussion (Misc queries) | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Excel 2007 Auto Filter Filter | Excel Discussion (Misc queries) | |||
Limit filter options in Auto Filter | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) |