Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then Macro in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then Macro in Excel
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 -- HTH Bob Phillips "Jen" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then Macro in Excel
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" "Jen" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then Macro in Excel
Thank you Bob, that worked great!
Have a wonderful day!! Jen "Bob Phillips" wrote: 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 -- HTH Bob Phillips "Jen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |