VBA - Check 3 rows if blank and display text in one of them in adjacent cell

[SOLVED]
Hi,

Hopefully, I'm explaining this properly...


I have 3 columns (ranges) C5:C1000, D5:D1000 and E5:E1000

I need VBA to compare adjacent cells (in the same row) in all 3 ranges and do the following:


If there's any text in a cell in E5:E1000 range and both adjacent cells in C5:C1000 and D5:D1000 ranges are empty (blank),

Then display "YES" in adjacent cell in range D5:D1000 - (in the same row)


Would look like something like this:

VBA Checks:

....C.......D......E
|.......|.......|any text|

after check, the same row becomes this:

|.......|YES |any text|


Thank you.

This should be faster in that we are not "selecting" cells needlessly before evaluating them, and this method will only loop through the cells that have actual data in column E, the others are skipped altogether:

Please Login or Register  to view this content.

Is there any way doing the same without using "Option Explicit"? I also have a lot of code before this code and option explicit messes up the whole thing..

Or if I place JBeaucaire solution in a separate sub in module (e.g.module1), how do I call it from my original code? My original code is located in classmodule

Thanks for your help guys.

Yes, you can take that out. No, you shouldn't.

Recommendation - use OPTION EXPLICIT at the top of all modules.

That parameter isn't "messing up the whole thing", it is specifically pointing out problems in the code so you can fix them. It's effectively a "code checker". Unless you're an ace at VBA, I would go into your VBA settings and turn that on so it appears automatically in any module you open.

In VBA: Tools > Options > Editor > [x] Auto Syntax Check


All that is "messing with" is telling you to go back and declare your variables properly. Plus, as you code from now on it will point out most errors as you make them. The result is hours saved in debugging. Priceless.

You call another SUB by using its name

Please Login or Register  to view this content.

JBeaucaire,

Yes, you are right, I'm fairly new to VBA.

Anyway, I've added "OPTION EXPLICIT", named all the variables - managed to get my original code working. Added your suggested sub at the bottom of my code. The next problem was it would display an error: "no cells were found", so I've changed the range E5:E1000 to E:E - that fixed the error.

ISSUE 2: Next, after some testing I found that your suggested code only works if I run it from a separate module (same as manually running macro 1, then, macro 2), because my original code populates the cells, therefore I can only run your suggested code after the initial subs (calculation etc) is complete.

So, now I'm basically facing with the problem of figuring out how to make JustSayYES sub run automatically only after my subs in a module are finished. Just adding "Call JustSayYES" sub - didn't work as described above in ISSUE 2, so, I added the sub into new module and called it using call module1.JustSayYES. Again, no success.

I'm assuming there's some way of scheduling to run a second macro after the first one is finished, but I'm not sure how to do that.. Also, my subs are inside Class Module.

update:

Ok, I've managed to solve the problem by creating a new module which calls 2 other modules, one after another. This also allowed me to name the range back to E5:E1000

Please Login or Register  to view this content.

I'm guessing this case is closed Thanks everyone for your help. If you know alternative solutions to mine, please post it below. I'll add [solved] in the next 48 hours

Thanks.

Your RunIt() solution is a good "master macro" approach. Good way of modularizing your subroutines.

However, you could add Call JustSayYES to the bottom of the Project macro. Your call.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.