Excel - Macro

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
NissargAsked:
Who is Participating?
 
tureConnect With a Mentor Commented:
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
 
tureCommented:
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
 
NissargAuthor Commented:
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
 
NissargAuthor Commented:
Have not run it yet, but I can see what you are saying.

Thanks Ture

Nissar
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.