Run macro only if

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

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!
 
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.
 
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,

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.
 
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

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
 
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.
 
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,

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.
 
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:
 
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
 
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.
Click to expand...
 
Back
Top