If then Macro in Excel

[Guest]

I'm tying to implement a macro which says, if cell A1 = 0 then "Message Box".
I'm not sure exactly what I'm doing wrong when writing it

I have a spreadsheet set up to do an Advanced Criteria Search. I have
set-up several macro buttons, which once pressed, ask you a question,
prompting you to fill in data. Once you enter the data, your Advanced
Criteria begins to elminate entries not necessary.

I need to implement another macro, this macro once pressed will launch 4 of
those macros above, which prompt for 4 different Advanced Criteria questions.
In cell "Criteria" (F26) it does a Dcount of the Data and show how many are
in the list. If this cell "Criteria" = 0, then I need a messages box that
says "There is no matching criteria with your data". Once you select ok, I
need the Advanced Criteria cleared.

This is what I have, but it doesn't work:

If Range("CRITERIA").Value = 0 Then
InputBox ("There is no matching data with your criteria")
Application.Goto Reference:="Criteriavalues"
Selection.ClearContents
Range("B2").Select
Range("Database").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("Criteria"), Unique:=False
End If

 

[Bob Phillips]

If Range("CRITERIA").Value = 0 Then
ans = Msgbox("There is no matching data with your criteria", vbOKCancel)
If ans = vbOK Then
Application.Goto Reference:="Criteriavalues"
Selection.ClearContents
Range("B2").Select
Range("Database").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= Range("Criteria"), Unique:=False
End If
End If

 

[Don Guillett]

I'm not sure this is what you want..

If Range("CRITERIA").Value = 0 Then
msgbox "There is no matching data with your criteria"

range("Criteriavalues").ClearContents
Range("Database").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("Criteria"), Unique:=False
End If

 

[Guest]

Where does the code error out, and what is the error you are getting?
The first thing I notice is the line:
InputBox ("There is no matching data with your criteria")
In general in VBA, when you use a method or function with the parentheses,
you need to assign the value to a variable - plus, in this case, InputBox is
typically used to supply a value input by your user (i.e. UserResponse =
InputBox(....)). I think what you want is this:
MsgBox "There is no matching data with your criteria"

 

[Guest]

Thank you Bob, that worked great!

Have a wonderful day!!
Jen