Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Autofilter on date in cell

Posted on 2007-04-08
3
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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 81

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

722 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