small error in vb code (autofilter/print) - guru needed

[fenster]

could any of u guru's please have a look at this code and tell me whats wrong..

the command button is on sheet 3 where cells D4 and G4 have from / to dates. i.e d4 = 01/03/03 g4 = 31/03/03


sheet one is called voucher -column 1 is a date column.

the code should goto sheet 1,highlight column1 filter it and print.


Private Sub CommandButton1_Click()

Dim StartDate As Date
Dim EndDate As Date

Range("D4").Value = "startdate"
Range("G4").Value = "enddate"

Sheets("Voucher").Select
Columns("A:A").Select
Range("A7:A2100").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=startdate", Operator:=xlAnd _
, Criteria2:="<=enddate"
Selection.PrintOut Copies:=1, Collate:=True

End Sub

 

[Joe4]

Why did you have to copy the value from column A to another form?

I am not an expert on filters, but it appears that it will always return the first value so that you can see the drop-down box. If you don't want to see the first row, insert a blank row and begin your filter there.

OK, I have one more idea. In cell D4, enter some date where the month and day are NOT the same number. Then run this macro:

Sub MyDateMacro()

    Dim MyDate As Date
    MyDate = Range("D4").Value
    MsgBox Format(MyDate, "dd/mm/yyyy")
    
End Sub

Does this return the date, EXACTLY as it appears in cell D4? If it does not, then we need to convert it in the macro.

EDIT: See above regarding filters and first row.

 

[fenster]

DATE DISPLAYS 28/11/2003

i inserted some blank rows for the filter drop down..

on testing when i dont get the results ,if i goto the filter dropdown and choose custom then the formula is all filled in => date <=date and when i click ok the results are displayed as should be .

its as if the code for the filter isnt doing the job but the filter itself is if used manually.

 

[Joe4]

Well, I am just about out of ideas. I can't recreate the problem, because it works fine for me.

One last thing, try posting your code one last time. I know we had made some changes, and I just want to see if it is the same as the code I am using.

 

[fenster]

Sub Macro9()

Dim StartDate As Variant
Dim EndDate As Variant

StartDate = Range("D4").Value
EndDate = Range("G4").Value

Sheets("Voucher").Select
Columns("A:A").Select
Range("A7:A2100").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd _
, Criteria2:="<=" & EndDate
'Selection.PrintOut Copies:=1, Collate:=True

(i have omtted the print line only to save paper!)

End Sub

 

[fenster]

its definatly a date format problem :

if i run a search for 03/01/03 the results are 01/03/03

thats whats wrong!

but ive checked everywhere u have asked me...


davie.

 

[Joe4]

I copied your code, exactly as you have it, and it works perfectly for me. I'm at a loss! The only thing I can think of is that there is something going on with your data.

By the way, why did you change the DIM on StartDate and EndDate to Variant?

 

[fenster]

somewhere in the code the date is being changed from 10/03/03 to 03/10/03 and likewise - i was just trying different things to try to find where this was happening.
I have it working great except for the date being mis-reported...
how do i get the sheet1 to show all records after print? - (uncheck filter)
by code?
btw - thanks for all ur help on this thread its really appriciated.

(just in case u HAVE ran out of ideas!)

 

[Joe4]

Regarding your last reply, that is what I thought might be going on!

Your dates are formatted as "dd/mm/yyyy". What is the Excel default date format where you are (I think it is different in the U.S. and Europe)?

Try playing around with :

StartDate=Format(Range("D4").Value,"dd/mm/yyyy")
EndDate=Format(Range("G4").Value,"dd/mm/yyyy")

If that doesn't work try, changing it to the Excel default format.

If that doesn't do it, also try playing around with DIM, changing it from Date or Variant to String.

Hopefully, one of those combinations will solve your problem!

 

[Joe4]

This code should set it back:

    Range("A7:A2100").AutoFilter

 

[fenster]

i found a bug - in step mode...

when the cursor passes over the
StartDate = Range("D4").Value
EndDate = Range("G4").Value

then the values are fine - they take the value of the date from / to from sheet 3 (reports)

but when the cursor passes to the line
Columns("A:A").Select
then the values are rewritten to correspond to cells D4,G4 of sheet1 (voucher)

this must be messing it up?