Criteria Fliter then Count results

  • Thread starter Thread starter pauluk
  • Start date Start date
P

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
 
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?).
 
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
 
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,
 
fixed it.

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

Thanks for your help
 
Back
Top