Solved

Autofilter on date in cell

Posted on 2007-04-08
3
216 Views
Last Modified: 2013-11-25
Hi, I have a macro that prompts the user to input the date they'd like to filter the data on.  The date they enter is placed in K3 on the worksheet.   All works well except the column is filtered on m/dd/yy.  I need dd/mm/yy.

Sub PAWPSCLOSEDU()
   Dim Date1 As Date
  Date1 = InputBox("Enter date for 'Closed since', eg 01/01/07", "Input Date")
   On Error GoTo 0
 If Date1 = #12:00:00 AM# Then
  MsgBox "No date entered"
  Exit Sub
 End If
 Range("k3").Value = Date1
 Range("k3").NumberFormat = "dd/mm/yy"
 
   Application.ScreenUpdating = False
      Range("k3").Select
     Selection.AutoFilter Field:=9, Criteria1:=">=" & Worksheets("Priority Action Work Plans").Range("k3").Value
    ActiveCell.FormulaR1C1 = Date1
    Range("A1").Select
    ActiveCell.Offset(4, 0).Select
    Application.ScreenUpdating = True
End Sub

Many thanks for any ideas.
0
Comment
Question by:Dalevb
  • 2
3 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 18873015
Try it like this. I added code to convert the date format from dd/mm/yy into mm/dd/yy. VBA uses the American style dates, which is why your original macro didn't work as intended.

Sub PAWPSCLOSEDU()
Dim Date1 As Date
Dim vDate As Variant
Dim sDate As String
sDate = InputBox("Enter date for 'Closed since', eg 01/01/07", "Input Date")
On Error GoTo 0
If sDate = "" Then
    MsgBox "No date entered"
    Exit Sub
End If

vDate = Split(sDate, "/")
If UBound(vDate) = 0 Then vDate = Split(sDate, "-")
Date1 = DateSerial(vDate(2), vDate(1), vDate(0))

Range("k3").Value = Date1
Range("k3").NumberFormat = "dd/mm/yy"

Application.ScreenUpdating = False
Range("k3").Select
Selection.AutoFilter Field:=9, Criteria1:=">=" & Worksheets("Priority Action Work Plans").Range("k3").Value
ActiveCell.FormulaR1C1 = Date1
Range("A1").Select
ActiveCell.Offset(4, 0).Select
Application.ScreenUpdating = True
End Sub


Brad
0
 
LVL 80

Expert Comment

by:byundt
ID: 18874022
Dalevb,
Thanks for the grade!
Brad
0
 

Author Comment

by:Dalevb
ID: 18874389
Thank you Brad!  (Not sure if my previous comment submitted properly.)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
problem with vb editor in excel 2016 10 25
TT Column Arrange 10 28
onOpen 14 42
Formula to convert ranked data 2 11
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now