Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Fliter then Count results
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Fliter then Count results
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?). -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Fliter then Count results
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Fliter then Count results
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, -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Fliter then Count results
fixed it.
chaged creteria:="=" & CLng(CDate(strCheck)) to creteria:=CDate(strCheck) Thanks for your help -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ms excel 2007 (Fliter and merge issue) | Setting up and Configuration of Excel | |||
Copy and paste when in fliter | Excel Discussion (Misc queries) | |||
Sum after Filter, Count after Fliter | Excel Worksheet Functions | |||
i can not fliter unique records | Excel Worksheet Functions | |||
criteria 1(a,b,c), criteria 2 (T,F) - Results (3 answers) achievab | Excel Discussion (Misc queries) |