+ Reply to Thread
Results 1 to 11 of 11

If cell is empty in a variable range, stop macro and return error message

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Sint-Niklaas, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Post If cell is empty in a variable range, stop macro and return error message

    Greetings to all

    First of all I'd like to thank all participants and moderators of this forum for the top excel solutions as I use countlessly solved threads to organise my work with excel.

    Currently, I attept to accomplish something in excel I haven't yet come accross on this forum and have no idea how to do it. I hope that guru's of vba will help me out on the following issue:

    I use 2monthly data sheet that contains approximately 30 columns and variable number of rows. There is a moment when I create dropdown lists for columns E (Main Choice) and F(Second Choice) which I'll combine using a hyphen in column G. The problem is that I want to check that all cells in column E are filled in or no cell in a range hasn't been left blank before I combine the texts . I also had an idea to highlight all empty cells using conditional formatting but figured out that all empty cells in the columns get highlighted, the effect I don't want to see. I want to generate a code that will check for blank cells in column E. IF columns E contains blank cells I want to quit my procedure and return an error message with indication of cells that are left blank. (e.g. if cells "E26" and "E122" are left blank the message will state: "Please, specify cells "E26", "E122"). In opposite case, I want my macro to continue working.
    Is this even possible?

    I hope I explained well my issue and I'm looking forward to getting a solution or a hint for it.

    Thanks in advance,
    Andrei Kononenko

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: If cell is empty in a variable range, stop macro and return error message

    Hi, Andrei,

    using ScpecialCells(xlCellTypeBlanks) comes to my mind when I read what you are after:

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    10-05-2012
    Location
    Sint-Niklaas, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: If cell is empty in a variable range, stop macro and return error message

    Hi Holger,

    The code you provided me with works as charm. I appreciate your help and mark this thread as solved.

    Many many thanks!

    Regards,
    Andrei

  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    Sint-Niklaas, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: If cell is empty in a variable range, stop macro and return error message

    Hi Holger,

    I think I was too rushy to mark this thread as solved. There are 2 problems with this code. First, the range for lngLast could be the entire column E in case all cells in E are left blank. Second, when I fill in at least 1 cell in the column the code doesn't seem to work anymore as it accepts all other blank cells in the column. Now, if I think correctly, I can replace "E" in lngLast = .Cells(Rows.Count, "E").End(xlUp).Row with "A" (rows.count for column A) and use that count in the next line. However, I can't figure out what's wrong with the code concerning the second issue.

    Can you, please, help me with that?

    Regards,
    Andrei
    Last edited by Andrei Kononenko; 10-30-2012 at 07:02 AM.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: If cell is empty in a variable range, stop macro and return error message

    Hi, Andrei,

    you´re correct about the failure if no entry inside the range has been made prior to running the code.

    Let´s try it this way:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  6. #6
    Registered User
    Join Date
    10-05-2012
    Location
    Sint-Niklaas, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: If cell is empty in a variable range, stop macro and return error message

    Hello Holger,

    This code works perfectly. Thank you very much for your assistance!

    Regards,
    Andrei

  7. #7
    Registered User
    Join Date
    06-15-2014
    Posts
    3

    Re: If cell is empty in a variable range, stop macro and return error message

    Hi all

    Just to add on with more criteria. If I want to find blank cells in Col E and F, how can I specific the range in the vba?

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: If cell is empty in a variable range, stop macro and return error message

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-15-2014
    Posts
    3

    Re: If cell is empty in a variable range, stop macro and return error message

    Oh wonderful! How about if Col E, Col F and Col H?

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: If cell is empty in a variable range, stop macro and return error message

    I tried just
    Please Login or Register  to view this content.
    but it failed on
    Please Login or Register  to view this content.
    This works
    Please Login or Register  to view this content.
    Last edited by nigelog; 03-22-2019 at 01:07 PM.

  11. #11
    Registered User
    Join Date
    06-15-2014
    Posts
    3

    Re: If cell is empty in a variable range, stop macro and return error message

    Thanks for the great help! It works perfectly. Last question, another check if column D is a date which is not today, it prompt msgbox”date incorrect.”

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1