Run macro only if
in [General]
|
From: puiuluipui on 25 May 2010 06:32 Hi, i cant make it work. The first one was simplier. I tried to copy the first macro twice, but it doesn't work this way. This new macro with concatenate it's more complicated. I can't make it work. I had JOHN in A1 and MARI in C1. In D1 i had nothing. And it doesn't work. What am i doing wrong? Thanks! "Mike H" a scris: > Hi, > > You could concatenate the 3 cell into one string and decide which sub to > call using select case > > Sub anothersub() > Set sht = Sheets("Sheet1") ' change to suit > With sht > mynames = .Range("A1") & .Range("C1") & .Range("D1") > End With > mynames = UCase(mynames) > Select Case mynames > Case "JOHNMARI" > Call thissub > Case "PETEJOE" > Call thatsub > Case "MIKEPUIULUIPUI" > Call theothersub > Case Else > MsgBox "Criteria not met" > End Select > End Sub > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "puiuluipui" wrote: > > > Hi, it's perfect! > > But i have one more question. Can this macro be made to run another macro if > > in A1 and C1 is another names? > > > > EX: > > A1 = John > > C1 = Mari > > D1 = "empty" > > If this criteria is met, then the macro to run MACRO 1 > > If in this cells i have: > > A1 = Jim > > C1 = Cris > > D1 = Monday > > and the macro to run MACRO 2. > > and if it's possible, the message box, to display the name of the macro that > > it's running (MACRO 1 or MACRO 2) > > > > Can this be done? > > Thanks!!! > > > > > > > > "Mike H" a scris: > > > > > Hi, > > > > > > Is this what you mean > > > > > > Sub somemacro() > > > Set sht = Sheets("Sheet1") ' change to suit > > > With sht > > > If UCase(.Range("A1")) <> "JOHN" _ > > > Or UCase(.Range("C1")) <> "MARI" _ > > > Or .Range("D1") <> "" Then > > > MsgBox "Criteria not met" > > > Exit Sub > > > End If > > > End With > > > 'Your code > > > > > > End Sub > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "puiuluipui" wrote: > > > > > > > Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and > > > > if D1 is blank. If this criteria is not mached, then the macro to display a > > > > message. > > > > Can this be done? > > > > Thanks!
From: Mike H on 25 May 2010 06:46 Hi, With John in a1 and mari in C1 it should have called a sub call "thissub" and if it didn't then what did it do? If it did nothing then were you using the correct sheet which was set up for sheet1 did you actually create a sub called "thissub"? Mine looked like this for testing purposes Sub thissub() MsgBox "thissub has been called" End Sub Did you get the message box "Criteria not met"? If you did then there was something wrong with the strings in the cells. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: > Hi, i cant make it work. The first one was simplier. I tried to copy the > first macro twice, but it doesn't work this way. > This new macro with concatenate it's more complicated. I can't make it work. > I had JOHN in A1 and MARI in C1. In D1 i had nothing. And it doesn't work. > What am i doing wrong? > Thanks! > > "Mike H" a scris: > > > Hi, > > > > You could concatenate the 3 cell into one string and decide which sub to > > call using select case > > > > Sub anothersub() > > Set sht = Sheets("Sheet1") ' change to suit > > With sht > > mynames = .Range("A1") & .Range("C1") & .Range("D1") > > End With > > mynames = UCase(mynames) > > Select Case mynames > > Case "JOHNMARI" > > Call thissub > > Case "PETEJOE" > > Call thatsub > > Case "MIKEPUIULUIPUI" > > Call theothersub > > Case Else > > MsgBox "Criteria not met" > > End Select > > End Sub > > > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "puiuluipui" wrote: > > > > > Hi, it's perfect! > > > But i have one more question. Can this macro be made to run another macro if > > > in A1 and C1 is another names? > > > > > > EX: > > > A1 = John > > > C1 = Mari > > > D1 = "empty" > > > If this criteria is met, then the macro to run MACRO 1 > > > If in this cells i have: > > > A1 = Jim > > > C1 = Cris > > > D1 = Monday > > > and the macro to run MACRO 2. > > > and if it's possible, the message box, to display the name of the macro that > > > it's running (MACRO 1 or MACRO 2) > > > > > > Can this be done? > > > Thanks!!! > > > > > > > > > > > > "Mike H" a scris: > > > > > > > Hi, > > > > > > > > Is this what you mean > > > > > > > > Sub somemacro() > > > > Set sht = Sheets("Sheet1") ' change to suit > > > > With sht > > > > If UCase(.Range("A1")) <> "JOHN" _ > > > > Or UCase(.Range("C1")) <> "MARI" _ > > > > Or .Range("D1") <> "" Then > > > > MsgBox "Criteria not met" > > > > Exit Sub > > > > End If > > > > End With > > > > 'Your code > > > > > > > > End Sub > > > > > > > > -- > > > > Mike > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > introduces the fewest assumptions while still sufficiently answering the > > > > question. > > > > > > > > > > > > "puiuluipui" wrote: > > > > > > > > > Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and > > > > > if D1 is blank. If this criteria is not mached, then the macro to display a > > > > > message. > > > > > Can this be done? > > > > > Thanks!
From: puiuluipui on 25 May 2010 07:04 Hi, it's working now, but it's not what i was looking for. I need 1 macro to look into these cells, and based on the content of this cells, to run another macro. The first one was perfect. Can the first macro be modified even without the message to run another macro based on the criteria in A1. C1, and D1? Can the first macro be modified? ...without any message? Thanks!!! "Mike H" a scris: > Hi, > > With John in a1 and mari in C1 it should have called a sub call "thissub" > and if it didn't then what did it do? > > If it did nothing then were you using the correct sheet which was set up for > sheet1 > > did you actually create a sub called "thissub"? Mine looked like this for > testing purposes > > Sub thissub() > MsgBox "thissub has been called" > End Sub > > Did you get the message box "Criteria not met"? > If you did then there was something wrong with the strings in the cells. > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "puiuluipui" wrote: > > > Hi, i cant make it work. The first one was simplier. I tried to copy the > > first macro twice, but it doesn't work this way. > > This new macro with concatenate it's more complicated. I can't make it work. > > I had JOHN in A1 and MARI in C1. In D1 i had nothing. And it doesn't work. > > What am i doing wrong? > > Thanks! > > > > "Mike H" a scris: > > > > > Hi, > > > > > > You could concatenate the 3 cell into one string and decide which sub to > > > call using select case > > > > > > Sub anothersub() > > > Set sht = Sheets("Sheet1") ' change to suit > > > With sht > > > mynames = .Range("A1") & .Range("C1") & .Range("D1") > > > End With > > > mynames = UCase(mynames) > > > Select Case mynames > > > Case "JOHNMARI" > > > Call thissub > > > Case "PETEJOE" > > > Call thatsub > > > Case "MIKEPUIULUIPUI" > > > Call theothersub > > > Case Else > > > MsgBox "Criteria not met" > > > End Select > > > End Sub > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "puiuluipui" wrote: > > > > > > > Hi, it's perfect! > > > > But i have one more question. Can this macro be made to run another macro if > > > > in A1 and C1 is another names? > > > > > > > > EX: > > > > A1 = John > > > > C1 = Mari > > > > D1 = "empty" > > > > If this criteria is met, then the macro to run MACRO 1 > > > > If in this cells i have: > > > > A1 = Jim > > > > C1 = Cris > > > > D1 = Monday > > > > and the macro to run MACRO 2. > > > > and if it's possible, the message box, to display the name of the macro that > > > > it's running (MACRO 1 or MACRO 2) > > > > > > > > Can this be done? > > > > Thanks!!! > > > > > > > > > > > > > > > > "Mike H" a scris: > > > > > > > > > Hi, > > > > > > > > > > Is this what you mean > > > > > > > > > > Sub somemacro() > > > > > Set sht = Sheets("Sheet1") ' change to suit > > > > > With sht > > > > > If UCase(.Range("A1")) <> "JOHN" _ > > > > > Or UCase(.Range("C1")) <> "MARI" _ > > > > > Or .Range("D1") <> "" Then > > > > > MsgBox "Criteria not met" > > > > > Exit Sub > > > > > End If > > > > > End With > > > > > 'Your code > > > > > > > > > > End Sub > > > > > > > > > > -- > > > > > Mike > > > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > > introduces the fewest assumptions while still sufficiently answering the > > > > > question. > > > > > > > > > > > > > > > "puiuluipui" wrote: > > > > > > > > > > > Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and > > > > > > if D1 is blank. If this criteria is not mached, then the macro to display a > > > > > > message. > > > > > > Can this be done? > > > > > > Thanks!
From: Mike H on 25 May 2010 07:22 Hi, You could do this and add ELSEIF statements for as many options as your require Sub somemacro() Set sht = Sheets("Sheet1") ' change to suit With sht If UCase(.Range("A1")) = "JOHN" _ And UCase(.Range("C1")) = "MARI" _ And .Range("D1") = "" Then Call thissub ElseIf UCase(.Range("A1")) = "PETE" _ And UCase(.Range("C1")) = "JOE" _ And .Range("D1") = "" Then Call thatsub End If End With End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: > Hi, it's working now, but it's not what i was looking for. I need 1 macro to > look into these cells, and based on the content of this cells, to run another > macro. The first one was perfect. > Can the first macro be modified even without the message to run another > macro based on the criteria in A1. C1, and D1? > Can the first macro be modified? ...without any message? > Thanks!!! > > "Mike H" a scris: > > > Hi, > > > > With John in a1 and mari in C1 it should have called a sub call "thissub" > > and if it didn't then what did it do? > > > > If it did nothing then were you using the correct sheet which was set up for > > sheet1 > > > > did you actually create a sub called "thissub"? Mine looked like this for > > testing purposes > > > > Sub thissub() > > MsgBox "thissub has been called" > > End Sub > > > > Did you get the message box "Criteria not met"? > > If you did then there was something wrong with the strings in the cells. > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "puiuluipui" wrote: > > > > > Hi, i cant make it work. The first one was simplier. I tried to copy the > > > first macro twice, but it doesn't work this way. > > > This new macro with concatenate it's more complicated. I can't make it work. > > > I had JOHN in A1 and MARI in C1. In D1 i had nothing. And it doesn't work. > > > What am i doing wrong? > > > Thanks! > > > > > > "Mike H" a scris: > > > > > > > Hi, > > > > > > > > You could concatenate the 3 cell into one string and decide which sub to > > > > call using select case > > > > > > > > Sub anothersub() > > > > Set sht = Sheets("Sheet1") ' change to suit > > > > With sht > > > > mynames = .Range("A1") & .Range("C1") & .Range("D1") > > > > End With > > > > mynames = UCase(mynames) > > > > Select Case mynames > > > > Case "JOHNMARI" > > > > Call thissub > > > > Case "PETEJOE" > > > > Call thatsub > > > > Case "MIKEPUIULUIPUI" > > > > Call theothersub > > > > Case Else > > > > MsgBox "Criteria not met" > > > > End Select > > > > End Sub > > > > > > > > -- > > > > Mike > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > introduces the fewest assumptions while still sufficiently answering the > > > > question. > > > > > > > > > > > > "puiuluipui" wrote: > > > > > > > > > Hi, it's perfect! > > > > > But i have one more question. Can this macro be made to run another macro if > > > > > in A1 and C1 is another names? > > > > > > > > > > EX: > > > > > A1 = John > > > > > C1 = Mari > > > > > D1 = "empty" > > > > > If this criteria is met, then the macro to run MACRO 1 > > > > > If in this cells i have: > > > > > A1 = Jim > > > > > C1 = Cris > > > > > D1 = Monday > > > > > and the macro to run MACRO 2. > > > > > and if it's possible, the message box, to display the name of the macro that > > > > > it's running (MACRO 1 or MACRO 2) > > > > > > > > > > Can this be done? > > > > > Thanks!!! > > > > > > > > > > > > > > > > > > > > "Mike H" a scris: > > > > > > > > > > > Hi, > > > > > > > > > > > > Is this what you mean > > > > > > > > > > > > Sub somemacro() > > > > > > Set sht = Sheets("Sheet1") ' change to suit > > > > > > With sht > > > > > > If UCase(.Range("A1")) <> "JOHN" _ > > > > > > Or UCase(.Range("C1")) <> "MARI" _ > > > > > > Or .Range("D1") <> "" Then > > > > > > MsgBox "Criteria not met" > > > > > > Exit Sub > > > > > > End If > > > > > > End With > > > > > > 'Your code > > > > > > > > > > > > End Sub > > > > > > > > > > > > -- > > > > > > Mike > > > > > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > > > introduces the fewest assumptions while still sufficiently answering the > > > > > > question. > > > > > > > > > > > > > > > > > > "puiuluipui" wrote: > > > > > > > > > > > > > Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and > > > > > > > if D1 is blank. If this criteria is not mached, then the macro to display a > > > > > > > message. > > > > > > > Can this be done? > > > > > > > Thanks!
From: puiuluipui on 25 May 2010 07:28 Hi, this is almost what i need. But if i have three macro at the end of your macro, it's not running the first or the second macro but the third. Can't this macro have my code in the middle of yours? Ex: Sub Macro1() Set sht = Sheets("Sheet1") ' change to suit With sht If UCase(.Range("A1")) = "JOHN" _ And UCase(.Range("C1")) = "MARI" _ And .Range("D1") = "" Then msg = MsgBox("Macro 1", vbInformation, "Running") 'my macro 1 Range("A1:C15").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Range("A1").Select 'end of my macro 1 ElseIf UCase(.Range("A1")) = "JIM" _ And UCase(.Range("C1")) = "CRIS" _ And UCase(.Range("D1")) = "MONDAY" Then msg = MsgBox("Macro 2", vbInformation, "Running") Macro2 Else MsgBox "Criteria not met" Exit Sub End If 'my macro 2 Range("A1:C15").Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Range("A1").Select End With 'end of my macro 2 End Sub Can this be done to avoid the third macro? Thanks! "Per Jessen" a scris: > Hi > > This should do it: > > Sub Macro1() > Set sht = Sheets("Sheet1") ' change to suit > With sht > If UCase(.Range("A1")) = "JOHN" _ > And UCase(.Range("C1")) = "MARI" _ > And .Range("D1") = "" Then > msg = MsgBox("Macro 1", vbInformation, "Running") > ElseIf UCase(.Range("A1")) = "JIM" _ > And UCase(.Range("C1")) = "CRIS" _ > And UCase(.Range("D1")) = "MONDAY" Then > msg = MsgBox("Macro 2", vbInformation, "Running") > Macro2 > Else > MsgBox "Criteria not met" > Exit Sub > End If > End With > 'Your code > > End Sub > > "puiuluipui" <puiuluipui(a)discussions.microsoft.com> skrev i meddelelsen > news:8DB11995-E689-40E2-BDDC-1D587FA84E15(a)microsoft.com... > > Hi, it's perfect! > > But i have one more question. Can this macro be made to run another macro > > if > > in A1 and C1 is another names? > > > > EX: > > A1 = John > > C1 = Mari > > D1 = "empty" > > If this criteria is met, then the macro to run MACRO 1 > > If in this cells i have: > > A1 = Jim > > C1 = Cris > > D1 = Monday > > and the macro to run MACRO 2. > > and if it's possible, the message box, to display the name of the macro > > that > > it's running (MACRO 1 or MACRO 2) > > > > Can this be done? > > Thanks!!! > > > > > > > > "Mike H" a scris: > > > >> Hi, > >> > >> Is this what you mean > >> > >> Sub somemacro() > >> Set sht = Sheets("Sheet1") ' change to suit > >> With sht > >> If UCase(.Range("A1")) <> "JOHN" _ > >> Or UCase(.Range("C1")) <> "MARI" _ > >> Or .Range("D1") <> "" Then > >> MsgBox "Criteria not met" > >> Exit Sub > >> End If > >> End With > >> 'Your code > >> > >> End Sub > >> > >> -- > >> Mike > >> > >> When competing hypotheses are otherwise equal, adopt the hypothesis that > >> introduces the fewest assumptions while still sufficiently answering the > >> question. > >> > >> > >> "puiuluipui" wrote: > >> > >> > Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", > >> > and > >> > if D1 is blank. If this criteria is not mached, then the macro to > >> > display a > >> > message. > >> > Can this be done? > >> > Thanks! > > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: automatically update the pivot table Next: Insert symbol as a header for a column |