View Full Version : [SOLVED:] Autofilter-if criteria doesn't exist?
davmec93
10-14-2010, 01:01 PM
Hello all. I need a little help. I have some code that formats a spreadsheet of mine and I'm using an auto filter go select a users inputed date range. I need to stop the code if the input date doesn't exist and to notify them with a message box that says something like..."No data found". Here is a chunk of the code I'm using.
[c:c].AutoFilter 3, InputBox("Please enter date")
Any help would be greatly appreciated! Thank you.
Bob Phillips
10-14-2010, 03:46 PM
Do the InputBox and Autofilter separately, setting a variable to the result of the InputBox, test that before calling the Autofilter.
davmec93
10-14-2010, 05:48 PM
Do the InputBox and Autofilter separately, setting a variable to the result of the InputBox, test that before calling the Autofilter.
Thank you for you for the reply!
So I should use the InputBox and make the user input a variable and then call that variable when I do the autofilter....
Bob Phillips
10-15-2010, 09:29 AM
Wells sort of, you set the variable to the inputbox in a single statement, and then use that variable in the autofilter.
davmec93
10-15-2010, 10:33 AM
Wells sort of, you set the variable to the inputbox in a single statement, and then use that variable in the autofilter.
This may be above my head (not hard to do) but I will try to do a some searching on how to get that accomplished.
Once again, thank you for the arrow that points in the right direction.
davmec93
10-15-2010, 10:48 AM
Why wouldn't this work as a solution?
strDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria:=strDate
davmec93
10-15-2010, 12:36 PM
Gheez....sometimes....
I figured out what I was doing wrong.
I changedstrDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria:=strDate
TostrDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria1:=strDate
I seem to be good to go now.
Bob Phillips
10-16-2010, 08:03 AM
Does that work? I would have done it like this
Dim dDate As Date
dDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria1:=Format(dDate, Selection.Cells(2, 1).NumberFormat)
Zack Barresse
10-16-2010, 10:23 AM
I would highly recommend adding some error handling to this, and test if dDate houses an actual value or not, as the user may have cancelled. When it comes to dates, I almost never let the user actually type in the value. Instead I generally make a userform where I can control the data being input. Sometimes it's a little over the top for simple items, but the headache it saves me is worth it. :)
davmec93
10-18-2010, 11:12 AM
Does that work? I would have done it like this
Dim dDate As Date
dDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria1:=Format(dDate, Selection.Cells(2, 1).NumberFormat)
Ya, it seems to work great. I don't think that the date is actually in a date format. It is a progress output in .csv format.
davmec93
10-18-2010, 11:15 AM
I would highly recommend adding some error handling to this, and test if dDate houses an actual value or not, as the user may have cancelled. When it comes to dates, I almost never let the user actually type in the value. Instead I generally make a userform where I can control the data being input. Sometimes it's a little over the top for simple items, but the headache it saves me is worth it. :)
I thought about the error handling and I'm currently trying to bash my way thru it. I consider my coding skills kinda link brain surgery with a chain saw.
This is actually for me and nobody else will be using it.
My next issues are
1. try and tackle are, what if the date I choose isn't there (if not it just seems to run forever and a day)
2. being able to select a date range.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.