Checking for empty cell in vba

[raschwab]

Hi,
I am in the middle of testing my first attempt at using VBA code and to my dismay am running into difficulties. I have used the following VBA code in a For loop to check for empty cells in a spreadsheet:

If Cells(i, 1).Value <> "" And Cells(i, 13).Value <> "" And Cells(i, 20).Value <> 0 And Cells(i + 1, 12).Value < Cells(i, 12).Value Then

The contents in Cell(i,1) is text from a list (can be blank). Cells(i,13) is a date and the contents of the other cells in the code contain numbers.

Each time I run the code I receive a message "Run time error 13: type mismatch". I suspect that I may have to dimension a variable or something of the like. I have searched various websites and have not been able to find something that works.

Appreciate assessment and advice.

 

[poolhall]

add:

Dim i As Long

at the beginning of your code.

 

[VoG]

Try

If (Cells(i, 1).Value <> "" And Cells(i,  13).Value <> "" And Cells(i, 20).Value <> 0) And (Cells(i + 1,  12).Value < Cells(i, 12).Value) Then

 

[raschwab]

peter,
Thanks for the quick response. I inserted the brackets as indicated; unfortunately the problem remains. Any other ideas? I suspect the solution will be quite trivial. It just remains elusive right now.

I am willing to provide the remaining code if you think this would be helpful.

 

[raschwab]

I inserted a "Dim i As Long" line at the beginning of the code and still no joy. The same error message comes up.

 

[Colin Legg]

Hi,

I am in the middle of testing my first attempt at using VBA code

Congratulations on taking the plunge into VBA...

For columns 1 and 13, if one of the cells contains an error then you will get a type mismatch error. The cleanest way to check if a cell is empty (no formula, no constant) is to use the IsEmpty() function.

If Not IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i,  13)) And Cells(i, 20).Value <> 0 And Cells(i + 1,  12).Value < Cells(i, 12).Value Then

If you still get the error then check columns 12 and 20. Are there any error values in there?

 

[raschwab]

Colin,
You hit the nail right on the head with respect to errors in column 20. I didn't change the coding to using "IsEmpty"; however, that was one of my attempted solutions before making my initial post.

Now on to the next glitch.

Thank you and to all the folks that responded. This was the first time I used this forum. It works fantasitically well.

Rick