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 commenting out the Columns("A:A").Select statement (or deleting it). I don't think it is doing anything anyway because it is superceded by the Range("A7:A2100").Select statement.

 

[fenster]

if i rem it out then the same error just comes up for the next line in code:
Range("A7:A2100").Select

tried unfreezing pane - changed nothing...

 

[Joe4]

Do you have any other merged cells in the spreadsheet, especially in column A? If so, that will probably create problems as well.

 

[fenster]

looked over column A:A and cant find any merged cells - even if i try any range in the
Range("A7:A2100").Select
line it still errors on same problem.
as i said i have a frozen pane on rows 1-6 but i unfroze it and same error.


man this is frustrating...

is there another way of selecting column a:a???

 

[Joe4]

I have re-created everything on my side and it works, using the exact code I gave you.

Try this macro:

Sub MySelectColumns()

    Columns("A:A").Select
    MsgBox Selection.Address
    
End Sub

It should return a message box that says "$A:$A".

If it doesn't work, there is something prohibited us from selecting cells in column A. You don't have any protected cells, do you?

 

[fenster]

yep works fine returns $a:$a

i put the whole code into a macro and it ran!

why wont it run from a cmdbutton when it will run from macro/run macro?

can i put code to the button : call macro9 or likewise?

also a small problem when i print it only prints the dates selected ,i want the complete row printed cols A-O...

 

[Joe4]

OK, sometimes VBA doesn't always tell you the correct line an error occurs on, so try this. If you run your macro through the drop down menu, you have the option to "Step Into" the macro. Select this option. Then minimize the VB editor box so that you can see both your spreadsheet and code at the same time. If you click F8, you will go through your code one step at a time. Keep hitting F8 to step through one step at a time. Then see exactly what is happening and why an error is occurring.

By the way, if you place the cursor over StartDate and EndDate in your macro after you have passed them, it should tell you their current values.

 

[fenster]

it will run now but only filters first record (row7) no matter what dates are entered.

ie i set start date 24/03/03
end date 24/03/03


there should be 4 records

but the macro just displays 25/02/03 which is row 7 the first row of the selection.

if i run the filter from the dropdown, on the top of the column, of the sheet then the 4 records are shown.

 

[Joe4]

It appears that it might not recognize column A as dates. Is column A entered and formatted as dates? Is it in the same format as D4 and G4?

A good test would be to locate a cell that has the same date as D4. If it were, for example, in cell A100, then in some empty cell, enter the formula =D4=A100. It should return the value of True. If it does not, our dates are in different formats.

If that does not appear to be the problem, please let me know what your date format is.

 

[fenster]

got a true value - had to copy cell from sheet voucher / pasted it in empty cell in sheet reports then did ur formula in empty cell

=H4=G4 and empty cell was = TRUE

should the filter drop down appear in cell 7 when the code is run?

some dates actuall work ok ,others seem to flash records and some dont seem to work at all - but the common thing is nomatter what date is entered when the filter runs the result is always that the first record (a7)
is always shown and the filter dropdown is also in cell (a7)

davie.


******************************************************

now were getting somewhere -

start date 03/05/03 end date 03/05/03 result should have been 43 records

but only one was displayed : dated 05/03/03 but the date formats are all the same!!

so is it
Dim StartDate As Date
Dim EndDate As Date

thats the culprit?