empty cell issue...

[marduk813]

Hey all. I'm having a weird issue that I just can't seem to resolve. I have a macro that runs through the cells in column A of my worksheet and looks to see which cells are empty and which contain *something*. It actually used to work fine, then the format of my spreadsheet changed, and suddenly my macro was reporting that no cell was empty. I added a few extra checks (just to cover more bases) and now only *some* of the empty cells are still passing the test (when I need them to fail). My cell-checking code looks like this now:

If (Not (InputFile.Range("A" & intCounter).Value = Empty)) And (InputFile.Range("A" & intCounter).Value <> "") And (InputFile.Range("A" & intCounter).Value <> " ") Then

First, I was only testing for a "" in the cell. Since that stopped working (and since I'm not sure exactly what Empty means), I added the test for Empty and the test for a space character.

What's happening here is that the If statement is evaluating to true even for cells that appear empty. There are no hidden characters, no formulae, nothing (that I'm aware of). I've even tried right-clicking the first offending cell in my input file and clearing the contents, but it keeps reading it as something other than empty.

Any suggestions?

TIA,

Jas

 

[firefytr]

How about something like this ..

If Trim(InputFile.Range("A" & intCounter).Value) = "" Then

You might be dealing with a space, as you've seen. Just make the two comparison's equal to test. It's always best to try and not have a cell with just a space(s) though.

HTH

-----------
Regards,
Zack Barresse

 

[marduk813]

Thanks for the reply. I tried your code as well, but the test still keeps passing on at least one empty cell. What I need is for the If statement to return true if the current cell contains some data, and false if it's empty. The first cell I'm testing contains data and teh If statement evaluates to true, which is fine. Following that cell are several empty cells, and the If statement evaluates to false on those (which is what I want). Another cell with data comes next, it works fine, then a few more empty ones, which fail (as expected). Then it comes to a cell that, by all accounts, is empty, but the If statement still evaluates to true and then my macro tries to perform several operations on it, but since it's apparently empty, I get errors.

Specifically, the cells that contain data will contain strings that I'm using the Split function on. When the value of intCounter becomes 20 (referencing cell A20), it somehow makes the If statement evaluate to true, then my macro tries to Split the contents of that cell and I get an "subscript out of range" error. I didn't foresee this being an issue since I thought my If statement would catch everything correctly.

 

[marduk813]

Ok, so I figured out the problem. Apparently, cell A20 really is blank, but I have a Do loop inside my If..Then statement that increments intCounter in such a way that cell A20 never gets evaluated by the If statement. Basically the Do loop isn't ending properly, so my Split function tries to split the value from a blank cell.

Thanks for your help Zack. Next time I'll try to make sure I have a legitimate problem with empty cells.

 

[firefytr]

LOL! No worries. Glad you got it figured out though.

-----------
Regards,
Zack Barresse