VBA to Clear contents from a range if text appears in a certain cell

[sherman51]

hi,

I am looking to clear the contents of E120:K152 if the word STOP appears in cell G1

I need to apply this to 60 seperate sheets
Thanks for any help
Paul

[sintek]

Something like this...All 60 sheets in same workbook?

Please Login or Register  to view this content.

[TMS]

Try:

Please Login or Register  to view this content.

[sherman51]

thank you for your response ... yes all 60 in the same workbook ... but there are other sheets in the workbook that I would not want this to apply to, so does that mean that I have to place this code in each sheet ?
cheers
Paul

[TMS]

No, the code, in both examples, loops through every sheet in the workbook. You would need to specifically include the worksheets you want tested or exclude those that you don't. Assuming there are less that you want to exclude, you'd need to create a list of them in the code, or on a worksheet.

That said, if you don't have STOP in cell G51 on the sheets, then the macros don't change anything.

[sintek]

No the sheets need to be excluded like this...

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

[sherman51]

thanks to you both for your help, much appreciated
Regards
Paul

[sintek]

Glad I could contribute...Tx for rep +

[TMS]

You're welcome. Thanks for the rep.

[sherman51]

I put the code as follows into a Module ..

Please Login or Register  to view this content.

but it has caused a problem with other code I have in another module as follows ..

Please Login or Register  to view this content.

Have I put it in the wrong place or is the clash inevitable
Thanks again ..

[sintek]

Your post does not comply with rule #3.
3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing.
So, do the following pls:
Edit your post, highlight your code and click the [#] button at the top of the post window.

has caused a problem with other code

Other code in same module....Could be because you have not declared variables...

[sintek]

....deleted....

[sherman51]

i tried that, but seems to have made no diference to the post, sorry I don't know what else to do ..
Cheers
Paul

[sintek]

Tried what...I asked that you edit Post 11
So, do the following pls:
Go to Post 11 and Edit your post by highlighting your code and click the [#] button at the top of the post window.

[Norie]

The only problem I can see is you haven't declared DataSht, DestSht or Cntr.

Add this at the top of the module just below Option Explicit.

Please Login or Register  to view this content.

[sherman51]

thats what i did .. shall i try to re-post ?

[sintek]

As per post 12...

Could be because you have not declared variables...

Do what Norie suggests in Post 16 and will solve...

[sherman51]

got it now thanks .. both start and end codes were going in together, my apologies ..

[sherman51]

will try that, thanks again guys ..

[sherman51]

I added the lines posted by Norie (post 16) but I still had the clash with other vba problem, also the "clear cell range " vba did not work .

I then tried the code posted by TMS (post3), this does not cause a problem with other vba but does not clear the cell range either ..

Any further advice would be much appreciated ..
Paul

[Norie]

Paul

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[sherman51]

i'd be happy to copy all the codes if that would help.. not sure that posting the workbook would help as there is a procedure to get teh links up and running from the source (which you would need to be subscribed to) in order to see if there's a clash ..

would that help ?
Thanks
Paul

[sintek]

Codes won't help...Just out of curiosity...Where did you put norie's declare code...

Edit...

but it has caused a problem with other code

You are still to tell us what problem it causes...error Message ?

[sherman51]

i'd be happy to copy all the codes if that would help.. not sure that posting the workbook would help as there is a procedure to get teh links up and running from the source (which you would need to be subscribed to) in order to see if there's a clash ..

would that help ?
Thanks
Paul

[sherman51]

Please Login or Register  to view this content.

changed "Stop" to In-Play and excluded the sheets that didn't need the code ...

[sintek]

There's your problem...
Norie's declare statements...
Should go below...Your other Module...the one causing the problems...

Please Login or Register  to view this content.

[sherman51]

"In-Play" turned out to be the text and not Stop .. or can you see something other ? ..

[Norie]

Sintek

The declarations should be at the top of the module, not in a sub, because they are used in two subs, TimePaste and Setup - see post #11

Not ideal I know but the code would need to be rewritten to avoid that.

[sintek]

@ Norie

Sorry did not notice that...Still a problem though...See post #26
Wrong module perhaps...
Public Declare?

[Norie]

sintek

To be honest I'm not actually sure what the problem is, the declarations don't actually have anything to do with the sub named Clear.

I think it might help to see all the code, the error message(s) and/or a sample workbook.

[sintek]

Yeah true Norie

OP is still to tell us what the errors are... So come on now Sherman51...get with the program...

[sherman51]

thanks for your help so far guys, I now seem to have another problem, when I open the macros to view/edit, I can't do anything with them, just keep getting a sound/ding .. I'll have to get back to a working copy and start again ..
I actually have to go shortly and so will pick it up tomorrow if thats ok ....
Thanks again
Paul

[sherman51]

This is all the vba code in the workbook ..

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

hope this helps ...
. and there are a couple of macros that I have recorded for copying and sorting ..
when teh vba hangs, i hit de-bug and the purple text above is what's highlited ..

Paul

[TMS]

You have two Workbook_Open routines. That can't be right.

When it hangs and you Debug it, what values do the public variables have?

Not sure if the value of having a separate Setup routine.

[sherman51]

sorry, not sure what the public variables are .. teh workbook runs fine, it was only when I addes todays code that there was a problem, and when i hit de-bug it highlited the code I made purple in teh above post (34) ..

[TMS]

These are the public variables

Please Login or Register  to view this content.

We're all guessing here. Please post the workbook so we can see it in context.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[sherman51]

Hi guys, apologies for the delay, ... life's distractions ..
I have found that this code works if I manually type into cell G1, but it dosent work when cell G1 changes from blank to having text in it ...
Is there a difference in a cell being "updated from an outside source" and manualy typing in a cell? and can I get around it ?
thanks
Paul

Please Login or Register  to view this content.

[TMS]

Well, two things. 1) you're using a Selection Change Event handler so you only have to select a/any cell and it will fire; you don't have to type anything. And 2) a Change Event handler only fires if you manually change a cell or you use code to change the value of a cell; it doesn't fire when the value of a formula changes.

[sherman51]

ok, thank you for your reply ...