ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Filtering inputted dates using criteria (https://www.excelbanter.com/excel-programming/357230-filtering-inputted-dates-using-criteria.html)

JayM[_2_]

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?



All times are GMT +1. The time now is 10:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com