Solved: autofilter-if criteria doesn't exist?

[davmec93]

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]

Do the InputBox and Autofilter separately, setting a variable to the result of the InputBox, test that before calling the Autofilter.

[davmec93]

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]

Wells sort of, you set the variable to the inputbox in a single statement, and then use that variable in the autofilter.

[davmec93]

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]

Why wouldn't this work as a solution?
strDate = InputBox("Please enter date")
Selection.AutoFilter Field:=3, Criteria:=strDate

[davmec93]

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]

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]

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]

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]

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.