VBA: Delete cell contents if other cell=1

[grevil]

Good afternoon,

I'm very much new to vba, even though I have some knowledge of excel.

What I'm looking for is some code that evaluates cells AP9,11,13,15,17,19 and if they have a value of 1 (derived from a formula), to clear the contents of the corresponding cells in O9,11,13,15,17,19. This needs to be applied to an entire workbook with multiple sheets.

I'm looking to learn more about vba, but I'm completely stuck on this at the moment, so any help would be much appreciated.

Cheers all,

George

[Olly]

Something like:

Please Login or Register  to view this content.

[grevil]

Cheers Olly,

Thanks for the rapid response!

When I run it I get the following message:

Run-time error '13'; Type mismatch

If ws.Cells(lRow, "AP").Value = 1 Then

Is then highlighted when I debug.

[Olly]

I can't recreate that error. Can you attach your workbook, please?

[ptmuldoon]

I'm pretty sure you can't mix and match the column letter with Cells.

Perhaps replace this:

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

[Olly]

I'm pretty sure you can't mix and match the column letter with Cells.

You really can. It works perfectly for me (on several machines!)

Go test it - in your immediates window, enter:

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Olly for your awesome and effective solutions ..
@ George : the error '13' is because of type mismatch and as you need to learn I expect you are trying to type the code yourself so may be when typing the variable lRow, may be you have typed 1Row (so you may typed one 1 instead of the letter L in this variable name)
If not you can upload sample of your workbook. May be there some sheets protected ..

[grevil]

So when cells O9,11,13,15,17,19 are filled, a timestamp is triggered in the corresponding cell in AO. When the difference is more than 1 second, the corresponding cell in AP is changed.

So if AP is "1" I want the corresponding O cell to be cleared.

The existing macro needs to be run for the time to update.

[grevil]

Thanks to everyone for getting back to me so quickly.

I copied and pasted Olly's suggested code, and likewise ptmuldoon's but still have the same error.

As, I say, I'm new to all this, so maybe there's a simpler way to do the whole shebang in vba.

[ptmuldoon]

You really can. It works perfectly for me (on several machines!)

Go test it - in your immediates window, enter:

Please Login or Register  to view this content.

Thanks, I wasn't aware you could do that.

Anyway. I got your same error. Give us a few minutes to troubleshoot

[Olly]

Aha, your cells in AP contain errors.

So we either need to handle that in the code, or change your formulas in AP.

Code:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Change one, or the other, or both, then you'll achieve what you want.

[YasserKhalil]

The result of the formula is the cause of the error. So yo have to add a line in the code like that

Please Login or Register  to view this content.

[ptmuldoon]

Yep.

The underlying issue is you don't have end date for for everyone in all the worksheets. Per Olly, Fixing the formula with IfError and/or the macro to account for it should fix it for you.

[grevil]

You guys are amazing!

I've updated both the AP formulae and the code. The run-time error has now disappeared but the cells in O aren't deleted.

I've uploaded the updated workbook, maybe it's just me missing something simple.

[Olly]

Your use of NOW() in the formulae is flawed - that's a volatile function, so whenever a calculation happens, all the cells referring to NOW() will change their value - and it looks like that's not what you want.

I'd suggest you start a new thread, as it's a very different issue to this thread....

In terms of the code, when I manually enter '1' in a cell in AP, then run the macro, it does what you asked.

[YasserKhalil]

I have tested your workbook and it works well. Enter the value 12:00 in column "AO" to get the value 1 in "AP" and run the code ..
The value in column "O" will be cleared....!!

[grevil]

OK. I've played with it a bit more today and I see that it works. Is it not possible though to have it continuously running when the worksheet is active, rather than run once and stop, once and stop, etc.

[YasserKhalil]

You may call the macro in worksheet change but I don't recommend that.. It is better to update your data with one click when needed ..

[grevil]

Cheers guys. You've been a great help but I need it to be running continuously so I'm going to start a new thread as suggested by Olly.