Checking for empty cell in vba

raschwab

New Member
Joined
Mar 26, 2011
Messages
15
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try

Rich (BB code):
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

 
Upvote 0
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.
 
Upvote 0
I inserted a "Dim i As Long" line at the beginning of the code and still no joy. The same error message comes up.
 
Upvote 0
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.

Rich (BB code):
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top