Run macro only if
in [General]
|
From: Per Jessen on 25 May 2010 14:39 See if this is what you need: 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 With .Range("A1:C15").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") 'my macro 2 With .Range("A1:C15").Interior .ColorIndex = 3 .Pattern = xlSolid End With .Range("A1").Select 'end of my macro 2 Else MsgBox "Criteria not met" Exit Sub End If End With End Sub Best regards, Per "puiuluipui" <puiuluipui(a)discussions.microsoft.com> skrev i meddelelsen news:8EB3597F-0B0A-4C59-B740-D05AFB0248C5(a)microsoft.com... > 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! >> >> . >>
From: puiuluipui on 27 May 2010 16:22 It's perfect!! Thanks alot!!!! "Per Jessen" a scris: > See if this is what you need: > > 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 > > With .Range("A1:C15").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") > 'my macro 2 > > With .Range("A1:C15").Interior > .ColorIndex = 3 > .Pattern = xlSolid > End With > .Range("A1").Select > > 'end of my macro 2 > Else > MsgBox "Criteria not met" > Exit Sub > End If > > End With > End Sub > > Best regards, > Per > > "puiuluipui" <puiuluipui(a)discussions.microsoft.com> skrev i meddelelsen > news:8EB3597F-0B0A-4C59-B740-D05AFB0248C5(a)microsoft.com... > > 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! > >> > >> . > >> > . >
From: puiuluipui on 27 May 2010 16:51 Thanks! "Mike H" a scris: > 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!
First
|
Prev
|
Pages: 1 2 3 Prev: automatically update the pivot table Next: Insert symbol as a header for a column |