Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering inputted dates using criteria
I'm looking for a way to have a macro use the a filter to filter out
dates using the greater/less than or equal to. The date needs to be changeable, so I have the user inputing the dates, but can't figure out how to get the advanced filter to work when inputing the date manually (via InputBox). This is as far as my macro goes for now, because I'm stuck: Sub SingleTrainingRecord() ' SingleTrainingRecord Macro Sheets("All Years").Select Dim Message, Title, Employee, Trainee, ColNo, StartDate, StopDate Message = "Enter employee's name" ' Set prompt. Title = "Select employee" ' Set title. ' Display message, title, and default value. Beep Employee = InputBox(Message, Title) If Employee = "" Then Exit Sub StartDate = InputBox("Enter the Start Date:") If StartDate = "" Then End StopDate = InputBox("Enter the Stop Date:") If StopDate = "" Then End On Error GoTo errHandler Cells.Find(What:=Employee, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Trainee = ActiveCell.Value ColNo = ActiveCell.Column Selection.Copy Sheets("Extract").Select Rows("1:1").RowHeight = 30 ActiveSheet.Paste Sheets("All Years").Select Columns(ColNo).Select Application.CutCopyMode = False Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=startdate" and criteria2:="<=stopdate" Columns("A:F").Select Selection.Copy Sheets("Extract").Select Range("A2").Select ActiveSheet.Paste Range("A1").Select Sheets("All Years").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Selection.AutoFilter Range("A1").Select Sheets("Extract").Select Exit Sub errHandler: If Err.Number = 91 Then Wrap$ = Chr$(10) + Chr$(13) MsgBox "Excel is having trouble finding the information you requested." & Wrap$ & Wrap$ & "Please ensure that the spreadsheet is open and that the trainee exists." & Wrap$ & Wrap$ & Wrap$ & "The name you typed was '" & Employee & "'. Please ensure that this is correct and then try again.", vbCritical, "Failed to find Trainee" Exit Sub Else If MsgBox(Err.Number & " - " & Err.Description, vbOKCancel) = vbCancel Then Exit Sub Resume End If End Sub Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAX value but with two comparison filtering criteria | Excel Discussion (Misc queries) | |||
Filtering with multiple criteria | Excel Discussion (Misc queries) | |||
Filtering by criteria within PV in Excel. | Excel Discussion (Misc queries) | |||
How do I ensure dates inputted are during the work week? | Excel Worksheet Functions | |||
Advanced Filtering criteria | Excel Programming |