# VBA loop empty cell validation



## DFlem (Dec 28, 2022)

Hi. I've been stuck on a portion of my code. I am attempting to loop through column B to find any empty cells, if the cell is empty then present msgbox then exit sub. If no cells are empty then once loop is complete present msgbox stating validation is complete. The error is when I run the code I automatically get the message box that no errors were found even though that is false. Please help if you can. I have attached the code I have written.


----------



## bbotzong (Dec 28, 2022)

Your code worked for me. 
Are you sure the cells are empty, and do not contain any non-printable characters? When downloading from our mainframe, we'd get ASCII character 160 appended to text data. This looks like a space, but TRIM() doesn't clean it. The CLEAN() function doesn't remove it either. Go to the right of the apparent blank cell. Assuming cell B5 is the one you see as blank, enter =len(b5) in cell C5. If you see a number, then that could be what's causing your problem. To fix that particular problem, add an OR statement like this:

   if range("B" & e).value = "" or Asc(range("B" & e)) >132 then

I've had problems with non-printable characters before.


----------



## macfuller (Dec 28, 2022)

Your construction is working for me.  (I suggest you enter the code using the VBA tags so people can copy it directly instead of posting an image.)

Your "empty" cells might not be truly empty. Use a LEN() function to check if they're truly a null value.  Control characters or spaces might have crept in.  If your work orders start with a specific set of alphanumeric characters then you might test for that as a positive indicator rather than a null value for a negative indicator.

_Sorry, I see bbotzong's reply hit while I was running my test.  I'm not sure how to delete this entry since it's redundant, but he has some good suggestions too._


----------



## DFlem (Dec 28, 2022)

bbotzong said:


> Your code worked for me.
> Are you sure the cells are empty, and do not contain any non-printable characters? When downloading from our mainframe, we'd get ASCII character 160 appended to text data. This looks like a space, but TRIM() doesn't clean it. The CLEAN() function doesn't remove it either. Go to the right of the apparent blank cell. Assuming cell B5 is the one you see as blank, enter =len(b5) in cell C5. If you see a number, then that could be what's causing your problem. To fix that particular problem, add an OR statement like this:
> 
> if range("B" & e).value = "" or Asc(range("B" & e)) >132 then
> ...


Thank you very much!


----------

