PDA

View Full Version : Dates and Filters



mattc1989
09-03-2014, 12:04 PM
Hello, so here is what I need. A bit of a challenge it seems.

I have a excel sheet that includes start dates and end dates. I am trying to create code or macro to have a box pop up and ask you to enter a date, i then want the sheet to filter the items based on if that date is the start date or the end date, OR is in between the start date and end date.

HELP PLEASE !!!! : pray2: :crying: :banghead:

mattc1989
09-03-2014, 12:05 PM
Thank you in advance!

mancubus
09-03-2014, 12:46 PM
welcome to vbax.

assuming topleft cell of your table is A1 and dates are in column C (column 3)



Sub FilterBetweenDates()

Dim strDate As String, FilterDate As Date
Dim StartDate As Long, EndDate As Long

strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
If IsDate(strDate) Then
FilterDate = DateValue(strDate)
Else
MsgBox "Please enter a valid date! Quitting..."
Exit Sub
End If

With Worksheets("MySheet") ' change worksheet name to suit
StartDate = .Range("K1").Value 'or StartDate = DateValue("09/01/2014")
EndDate = .Range("K2").Value 'or EndDate = DateValue("12/31/2014")

If FilterDate >= StartDate And FilterDate <= EndDate Then
.Cells(1).AutoFilter Field:=3, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
End If
End With

End Sub

mattc1989
09-03-2014, 12:58 PM
After I enter the date i get a error "subscript out of range"


welcome to vbax.

assuming topleft cell of your table is A1 and dates are in column C (column 3)



Sub FilterBetweenDates()

Dim strDate As String, FilterDate As Date
Dim StartDate As Long, EndDate As Long

strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
If IsDate(strDate) Then
FilterDate = DateValue(strDate)
Else
MsgBox "Please enter a valid date! Quitting..."
Exit Sub
End If

With Worksheets("MySheet") ' change worksheet name to suit
StartDate = .Range("K1").Value 'or StartDate = DateValue("09/01/2014")
EndDate = .Range("K2").Value 'or EndDate = DateValue("12/31/2014")

If FilterDate >= StartDate And FilterDate <= EndDate Then
.Cells(1).AutoFilter Field:=3, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
End If
End With

End Sub

mattc1989
09-03-2014, 12:59 PM
I have two date rows that I want to compare with N & O

mancubus
09-03-2014, 02:24 PM
can you post your workbook (replace sensitive data with fake data)?

go advanced
manage attachments

snb
09-03-2014, 11:55 PM
Why don't you use a table or autofilter.

mattc1989
09-04-2014, 05:26 AM
I am trying to make this as easy as possible for the end user.


Why don't you use a table or autofilter.

mattc1989
09-04-2014, 05:27 AM
attached below

mancubus
09-04-2014, 11:17 PM
this is different. you have separate columns for Start end End dates. and values in these columns are strings not dates.

i will work on it.

mancubus
09-04-2014, 11:36 PM
since you just want to filter the rows, i used a helper column, Column Y, to apply a filter.

assumption: values in Column N and O are always in this format: DDD MM/DD/YYYY HH:MM AM/PM



Sub FilterBetweenDates()

Dim strDate As Date, FilterDate As Date
Dim StartDate As Long, EndDate As Long, i As Long

strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
If IsDate(strDate) Then
FilterDate = DateValue(strDate)
Else
MsgBox "Please enter a valid date! Quitting..."
Exit Sub
End If

With Worksheets("Sheet1")
.AutoFilterMode = False
.Range("Y1") = "BetweenStartEnd"
For i = 2 To .Cells.Find("*", , , , xlByRows, xlPrevious).Row
StartDate = DateValue(Mid(.Range("N" & i), 5, 10))
EndDate = DateValue(Mid(.Range("O" & i), 5, 10))
If FilterDate >= StartDate And FilterDate <= EndDate Then
.Range("Y" & i) = "Between"
End If
Next i
.Cells(1).AutoFilter Field:=25, Criteria1:="=Between"
End With

End Sub

snb
09-05-2014, 12:07 AM
In the attachment:

how I converted text to dates (one liner macro).

The conversion of the data into a table. (Excel >2003)

Now the user can select any criterion to filter on.

mattc1989
09-09-2014, 05:29 AM
Hello, i entered 10/01/2014 and i received a VB error "Run-time error "13": Type mismatch"

How can i fix that?

** This is highlighted ** See below

EndDate = DateValue(Mid(.Range("O" & i), 5, 10))


since you just want to filter the rows, i used a helper column, Column Y, to apply a filter.

assumption: values in Column N and O are always in this format: DDD MM/DD/YYYY HH:MM AM/PM



Sub FilterBetweenDates()

Dim strDate As Date, FilterDate As Date
Dim StartDate As Long, EndDate As Long, i As Long

strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
If IsDate(strDate) Then
FilterDate = DateValue(strDate)
Else
MsgBox "Please enter a valid date! Quitting..."
Exit Sub
End If

With Worksheets("Sheet1")
.AutoFilterMode = False
.Range("Y1") = "BetweenStartEnd"
For i = 2 To .Cells.Find("*", , , , xlByRows, xlPrevious).Row
StartDate = DateValue(Mid(.Range("N" & i), 5, 10))
EndDate = DateValue(Mid(.Range("O" & i), 5, 10))
If FilterDate >= StartDate And FilterDate <= EndDate Then
.Range("Y" & i) = "Between"
End If
Next i
.Cells(1).AutoFilter Field:=25, Criteria1:="=Between"
End With

End Sub

mancubus
09-09-2014, 06:14 AM
you dont need to quote previous messages.

perhaps have nonblank cells below your table.

change

.Cells.Find("*", , , , xlByRows, xlPrevious).Row

to

.Cells.(.Rows.Count, 1).End(xlUp).Row
or if cells in Column A are contiguous range

.Cells(1, 1).End(xlDown).Row

mattc1989
09-09-2014, 06:24 AM
How should i enter the date ?? 10012014 or 10/01/2014 ??

mattc1989
09-09-2014, 06:26 AM
it still highlights EndDate = DateValue(Mid(.Range("O" & i), 5, 10))

mattc1989
09-09-2014, 06:28 AM
When i enter For i = 2 To .Cells.(.Rows.Count, 1).End(xlUp).Row it turns red and I cannot run the macro

mattc1989
09-09-2014, 06:32 AM
Just to be clear on what I need....If I enter October 1, 2014 as the date I want any row that has a start date of Oct 1 or a end date of Oct 1 OR Oct 1 is between the start and end date.

Is that what you provided?

mancubus
09-09-2014, 08:16 AM
what is international date format for your PC? mm/dd/yyyy or dd/mm/yyyy.

when dealing with dates in VBA with a format other than mm/dd/yyyy format, it's likely that you face problems.

DateSerial function is for handling the date format issues.

try below code to see which dates are returned for, say, Mon 09/01/2014 09:00 am, in column Y.
is it 09.01.2014 or 01.09.2014



Sub FilterBetweenDates2()

Dim strDate As Date, FilterDate As Date
Dim StartDate As Long, EndDate As Long, i As Long

strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
If IsDate(strDate) Then
FilterDate = CDate(strDate)
Else
MsgBox "Please enter a valid date! Quitting..."
Exit Sub
End If

With Worksheets("Sheet1")
.AutoFilterMode = False
.Range("Y1") = "Start"
.Range("Z1") = "End"
.Range("AA1") = "BetweenStartEnd"
For i = 2 To .Cells.(.Rows.Count, 1).End(xlUp).Row
.Range("Y" & i) = CDate(Mid(.Range("N" & i), 5, 10))
.Range("Z" & i) = CDate(Mid(.Range("O" & i), 5, 10))
If .Range("Y" & i) <= FilterDate And FilterDate <= .Range("Z" & i) Then
.Range("AA" & i) = "Between"
End If
Next i
.Cells(1).AutoFilter Field:=27, Criteria1:="=Between"
End With

End Sub

mattc1989
09-09-2014, 08:25 AM
Think its resolved, since one or two dates were blank, seems to be working :) thanks