Solved

Excel - Macro

Posted on 2000-04-10
4
151 Views
Last Modified: 2010-05-02
I am trying to use selection.autofilter in my sub procedure. I have declared two variables newdate and newdate1 as variants and formatted them as "dd/mm/yyyy" My problem comes when I try to use them in the selection.autofilter the first variable is inserted in criteria1 which produces the correct result "dd/mm/yyyy" however, the second variable which I put into criteria2 comes up with the DateValue instead of the actual typed in date. Why is criteria2 not appearing in the format "dd/mm/yyyy" when they are both declared and processed in the same fashion?

Thanks
0
Comment
Question by:Nissarg
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:ture
ID: 2700900
Nissarq,

If I'm not wrong, VBA assumes that you use mm/dd/yyyy (not dd/mm/yyyy). That is the cause of your problems. To avoid misunderstandings, you can use YYYY-MM-DD format or even better DateSerial(y,m,d), as shown in this example:

Sub AutoFilterTest()
  'Declare variables
  Dim ws As Worksheet
  Dim d1 As Variant
  Dim d2 As Variant

  'Set values of variables
  Set ws = ActiveWorkbook.Sheets("Sheet2")
  d1 = DateSerial(1999, 9, 1)
  d2 = DateSerial(1999, 12, 31)

  'Make sure that AutoFilter is turned off
  ws.AutoFilterMode = False

  'Use AutoFilter to display records from d1 to d2
  ws.Range("A1").AutoFilter _
    Field:=1, _
    Criteria1:=">=" & d1, _
    Operator:=xlAnd, _
    Criteria2:="<=" & d2
End Sub

Ture Magnusson
Karlstad, Sweden
0
 

Author Comment

by:Nissarg
ID: 2721739
Hi Ture

Thanks for your response. This client insists on having the dd/mm/yyyy format. When I run my macro on NT Server it works when the locale settings are dd/mm/yyyy. Is there no way to change this on Windows 98. I have tried the Regional Settings - doesn't help.

Thanks
Nissar
0
 
LVL 22

Accepted Solution

by:
ture earned 50 total points
ID: 2721833
Nissar,

It is ok that your client wants dd/mm/yyyy date format. You'll just have to make sure that VBA interprets the date correctly.

If I am not mistaken, the CDATE function will convert a text string to a date, using the windows regional settings. Try this little prrocedure to check out the CDATE function and see if it reports the correct month.

Sub TestCDate()
  Dim t As String
  Dim d As Date
  t = "01/07/2000"
  d = CDate(t)
  MsgBox Month(d)
End Sub

/Ture
0
 

Author Comment

by:Nissarg
ID: 2768985
Have not run it yet, but I can see what you are saying.

Thanks Ture

Nissar
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

792 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