application input box return different date formats leading to wrong resutl in auto filter

[shaukat]

Dear all,

I am working on a report which is attached along with the code.Application input box takes date and the filters coulmn"A" according to this date. Problem is that some values it returns correctly for example 5/5/2012, 6/5/2012, 7/5/2012 and data filter is ok BUT when I put 20/5/2012 then filter works wrongly returning no data at all. program hangs up.

secondly, at line 135 of code, if the date is wrong due to filter returning empty fileds and content of "Raw" sheet is empty then workbook must close, but it is hanged up due to above malfunctioning

any help is appreciated!

BR,
Shaukat

[Steffen Thomsen]

Try looking into CDate.

It converts a datatype to date datatype.

dt1 = Format(CDate(inputbox("Enter date in format d/m/yyy")),"d/m/yyy")

[shaukat]

thanks Thomsen, date format is fixed . but filter still gives empty fields on the value for example 20/05/2012. then progam hangs

thanks for help

[Steffen Thomsen]

First off all i would suggest you clean up the code a bit, before troubleshooting this error.

Your code does a lot of selecting and activating, wich slows down the code, and makes it harder to read.

Example:

    Sheets("sheet1").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "d/m/yyyy"

Can be rewritten like this

      Sheets(1).Range("A2:A" & Range("A2").End(xlDown).Row).format = "d/m/yyyy"

Or

The whole column

      Sheets(1).Columns(1).Format = "d/m/yyyy"

Try and clean the code up a bit, then maby the error will be more visible for you.

[shaukat]

thanks for help. problem solved using this approach:

Selection.NumberFormat = m / d / yyyy