Criteria Fliter then Count results

[pauluk]

Hi,

This what i require to do is after an input box pops up enter a dat
this then filters the criteria for that date and counts how man
results are ther.

The code i am using is below but i have problems. If a use text it i
fine and works but when i chage the code for a date it doesn't the dat
code is below.

Dim rng As Range
Dim rng2 As Range
Dim strCheck As String

Sheets("Telephone Stats").Select

strCheck = Application.InputBox("Please enter date you require data fo
(date must be enter dd/mm/yyyy)", "Manager Checks")

Selection.AutoFilter Field:=7, _
Criteria1:="=" & CLng(CDate(strCheck))

Set rng = ActiveSheet.AutoFilter.Range

MsgBox rng.Columns(1). _
SpecialCells(xlVisible).Count - 1 _
& " of " & rng _
.Rows.Count - 1 & " Records"

Sheets("Manager Checks").Select

End Su

 

[kkknie]

If I remove the CLng() conversion, I get it to work. What is you
specific problem (i.e. how is it not working, errors?, no data?).

 

[pauluk]

Even if i romove the CLng i still get no results returned.

The only additional items with the data is there is a form and and
hidden row but these are all above the data

 

[kkknie]

I'm headed out for the day, but here's a thought.

Do a comparison on the cells to see if anything is returned with a loo
so you can print out what the code is seeing. Something like:


Code
-------------------
Sub test()
Dim rng As Range
Dim rng2 As Range
Dim strCheck As String

Sheets("Telephone Stats").Select

strCheck = Application.InputBox("Please enter date you require data for (date must be enter dd/mm/yyyy)", "Manager Checks")

Set rng = Range("G1:G100")

For each rng2 in rng

debug.print rng2.Value & " = " & strCheck 'Shows check
debug.print rng2.Value = strCheck 'True or False

'or
debug.print clng(rng2.Value) & " = " & CLng(CDate(strCheck)) 'Shows check
debug.print clng(rng2.Value) = CLng(CDate(strCheck)) 'True or False

Next

End Su
-------------------

Where G1:G100 is the range you do your autofilter. Also, change th
debug statement to print many different variations of checking unti
you find one that works.

Good luck,

 

[pauluk]

fixed it.

chaged creteria:="=" & CLng(CDate(strCheck))
to
creteria:=CDate(strCheck)

Thanks for your help