VBA Loop + If Does not "recognize" empty cells, but inserting a formula in cell does?

[Norcal1]

Working on another macro (still green here) to reformat some data in a table that involves adding some headers and formatting cells. When I am running into is this:

In column E and F, most rows (the data points) have either a number or a description in the cell. Every so often, there is a row that has no value in E or F (this will be a header row for the data below it). There are just under 500 rows in all.

I can loop through the rows fine, and in column D I can use a VBA loop to insert a formula into any column D cell in which that row has no value in E or F like so:

Please Login or Register  to view this content.

This works fine and it ONLY puts the formula into the cells in column D where the row is otherwise empty in E and F (I'm just referencing E as the IF since E and F are always empty together).

However, when I try to loop through with a For + and If statement in VBA to copy the value in D into E (or even just add a "Yes" as a test out in Column N or something), the vba loop and If statement do no recognize E or F as empty. That code is:

Please Login or Register  to view this content.

I've got the copy and paste "turned off" with a comment flag for now, but as this loops through all ~500 rows, it does not insert a "Yes" into column 16 in any of the rows in which column 5 (column E) is empty.

I have tried this with = "", with IsEmpty() = True, and I have cycled through tons of these rows to confirm there is no value in any of them and there does not appear to be anything. I am assuming that I am missing something fairly simple here as I have managed to cook up quite a few other loops that reference if a cell or adjacent cell is empty without issue. Any help appreciated, this is basically my last step (I think) to completing this Macro and putting it to use.

This is what the macro runs to, and what should be happening is the stuff in column D gets copied into Column E where the cells are colored up:Untitled.jpg



Thanks

[1aaaaaaaaaaaaaaa]

Try changing to...

Please Login or Register  to view this content.

[Norcal1]

That unfortunately did not work either, thanks for the suggestion though.

What is odd also is that every one of those rows that I want the copying to occur in started life via an insert x1ShiftDow n also, so there was never any data in those cells to begin with that was maybe later manipulated. Very strange.

This is the complete code (which I am guessing is very messy and inefficient as I'm slowly self teaching for the most part). Everything else seems to be working just fine.

Please Login or Register  to view this content.

Thanks,

[Norcal1]

Also, if I run some small piece of code after the big macro:

Please Login or Register  to view this content.

To test if it is seeing the cell as empty, it does (and I tried it for a bunch of the empty cells and some non-empty cells).

However when I incorporate this into the larger macro, it still does not seem to recognize the empty cell because it does not add a "yes" to each empty row in column 16.

[1aaaaaaaaaaaaaaa]

Are you using merged cells by chance? That could throw a wrench in things.

Have you tried

Please Login or Register  to view this content.

And see what VBA thinks the value is?

[1aaaaaaaaaaaaaaa]

Can you upload a sample workbook to test with? So I can step through the code?

[Norcal1]

Random sample file attached (exact same initial formatting as my end data would start with.

I will try the msgbox and see what it does. Also all merged cells should get unmerged at the beginning so there shouldn't be any left that I am aware of other than E:M being merged in the rows that are "blank". Eventually they will need to be merged as shown in that screenshot so I added it in, but the if statement failed to find E or F empty even when I had not yet added the merging code so it seems to be irrelevant. I also tried it running on any row in which D is NOT empty since that corresponds to the same rows (NOT empty in D is Empty in E and F until this last bit I can't figure out runs).

[Norcal1]

Tried the msgbox, it also seems to think that the cells are empty (even when they are merged).