Results 1 to 7 of 7

Checking for empty cell in vba

This is a discussion on Checking for empty cell in vba within the Excel Questions forums, part of the Question Forums category; Hi, I am in the middle of testing my first attempt at using VBA code and to my dismay am ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Checking for empty cell in vba

    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.

  2. #2
    Board Regular poolhall's Avatar
    Join Date
    Jan 2009
    Location
    NYC
    Posts
    350

    Default Re: Checking for empty cell in vba

    add:

    Dim i As Long

    at the beginning of your code.

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,565

    Default Re: Checking for empty cell in vba

    Try

    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
    

    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Re: Checking for empty cell in vba

    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.

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Re: Checking for empty cell in vba

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

  6. #6
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,414

    Default Re: Checking for empty cell in vba

    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.

    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?

  7. #7
    New Member
    Join Date
    Mar 2011
    Posts
    15

    Default Re: Checking for empty cell in vba

    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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com