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]

Try changing:

Selection.AutoFilter Field:=1, Criteria1:=">=startdate", Operator:=xlAnd _
, Criteria2:="<=enddate"

to

Selection.AutoFilter Field:=1, Criteria1:=">=" & startdate, Operator:=xlAnd _
, Criteria2:="<=" & enddate

(when you put it inside the quotes, it is no longer a variable, but instead treated as literal text)

 

[Joe4]

Actually, I don't think you dates are defined properly either:

Private Sub CommandButton1_Click() 

Dim StartDate As Date 
Dim EndDate As Date 

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 

End Sub

 

[fenster]

thanks for the posts

i copied and pasted the revised code into the button and now

line 3 gives me a type mismatch error

any takers?

 

[Joe4]

What is line 3? Is it where we are defining EndDate and StartDate? Are these dates actually found in D4 and G4 (no blanks), and are they actually formatted as dates on the spreadsheet?

If this is not where the problem is occurring, please list the actual line of code that is giving the problem instead of a line number.

 

[fenster]

if i use:
Range("D4").Value = "startdate"
Range("G4").Value = "enddate"

this line errors:

Columns("A:A").Select

if i use :

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

i get a type mismatch on the startdate = line

 

[fenster]

line 3 represents 2 cells in the sheet with the cmdbutton

d4 is actually d4 and e4 merged
g4 " " g4 and h4 merged

and are formatted as dates dd/mm/yyyy


see other post for errors and where they are happening...

also - when the dates are in d4 and g4 - 01/03/2003 , 31/03/2003

when i press the command button then d4s date is replaced with text "startdate" and g4 has "enddate" just text!!
thanks

 

[Joe4]

If you ran the code using:

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

you essentially reset the value of D4 to the string value "startdate" and likewise, you reset the value of G4 to the string value "enddate".

Recall what I said, anything contained inside quotes is treated as a literal text string and not a Variable! Never put a variable inside quotes.

Besides that, the order of setting the variable above is backwards.

You need to use the update code I gave you:

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

Go back and check the value of D4 and G4. If you ran the other code first, you probably changed them to the text strings mentioned above. That is why you are probably getting a mismatch.

 

[Joe4]

Ah ha!

Please unmerge your cells. VBA hates merged cells and doesn't play nicely with them. Filters and sorts also do not liked merged cells.

General advice, avoid merging cells whenever posssible!

 

[fenster]

Jmiskey,

i have changed the merged cells into true d4 , g4 both cells formatted to
dd/mm/yy.

when i run the code sheet voucher is shown , cell 7 of sheet has focus but error message: select method of range class failed (1004).

and debug shows this line as culprit:
Columns("A:A").Select


i have a frozen pane in sheet 1 which holds rows 1-6...

could this be the problem?


heres the code again:
Private Sub CommandButton1_Click()

Dim StartDate As Date
Dim EndDate As Date

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

End Sub

any ideas?