Help with, if contains text

[Largey980]

Hi,

I am after some help with macro, I have multiple macros running and want to add check if statement to check correct data entered, if not stop all macros.

--------------
So after a paste

If "A1" does not contain specific "text", MsgBox "must contain this xyz", Stop All other subs
Else continue


--------------
Any help appreciated

 

[Joe4]

It really depends on how you have structured your code, and all these other Macros are being called to run, as well as this current check is going to reside (which macro is it found in)?
Can you post that information?

 

[Largey980]

Sub Runall
Call Setauto (set process automatic)
Call Paste (paste in data - check to be added and stop other subs running <<-- )
Call Copyandreplace (copy and replace to another location)
Call Del (delete pasted data)

Data is pasted into A1, so if statement could be added to end, it is however a paste special so don't think can undo

If the specific text is missing from A1 im after msgbox, and stop the others subs running.

hopefully this helps

 

[Joe4]

One thing you could do is establish a Global Variable, maybe a boolean data type, and call it ContinueMacros.
Initially set it equal to True.
Then, if anything happens in any one of your Macros that you want to stop everything, change its value to False.
Then, in your main "Runall" macro, before each step where you call another macro, add a line like this:

If ContinueMacro = False Then Exit Sub

One other note. Do NOT choose reserved words like "Paste" as the name of Macros or Variables. Using reserved words can confuse VBA (it isn't sure if you are referring to the variable, procedure, function, or property), and you could have unintended results.

 

[Largey980]

This sounds like a good idea, and I will add them in. I don't think its lets you use "paste" as I did try this(learning the hard way), just added it in for an example to keep it simpler.

I am still stuck with my original problem, I still need little bit of help with the: If "A1" does not contain "xyz" error msgbox.., stopping can work via If ContinueMacro = False Then Exit Sub as suggested.

 

[Largey980]

If Not (Range("A1").Value) = "XYZ" Then
MsgBox "Please retry and include xyz"
ContinueMacro = false
Exit Sub
End If

At a quick guess along the lines of the above with IF continueMacro = False then exit sub
not sure if this will capture the specific text I'm searching as it could be contained within other text in A1 eg "XYZPPP1"

 

[Joe4]

You can use the INSTR function to see if a string is contained within another string (see: MS Excel: How to use the INSTR Function (VBA))
So, it would look something like:

If INSTR(Range("A1"),"XYZ") = 0 Then
[COLOR=#333333]   MsgBox "Please retry and include xyz" 
[/COLOR][COLOR=#333333]   ContinueMacro = false
   Exit Sub
End If[/COLOR]

 

[Joe4]

Also note that if case doesn't matter (you want to treat "xyz" and "XYZ" the same), then you need a slight modification, i.e.:

If INSTR(UCASE(Range("A1")),"XYZ") = 0 Then

MS Excel: How to use the UCASE Function (VBA)