+ Reply to Thread
Results 1 to 4 of 4

Checking for blank cells before running macro

  1. #1
    Registered User
    Join Date
    05-13-2008
    Posts
    10

    Checking for blank cells before running macro

    Hi all,

    I have a quotation system that takes information from various cells dotted around a worksheet, and places them along a line per quote in a separate worksheet and returns a final price.

    However, I can leave blank cells and the quote will still occur, because there is no way at the moment that the macro can check whether cells are empty and skip the quoting process.

    I'm unsure how to check for blank cells efficiently, so that should one be blank the process stops - any ideas?

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    One way would be to keep a single column table of cells, each of which points to the cells that contain quotation information. i.e. assuming F18 & H23 happen to be two arbitrary quote information cells, in a table, say A5:A10 on a new sheet enter

    Please Login or Register  to view this content.
    Now in your macro just test for a zero value in A3 and if False exit the macro.

    HTH

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    What is your existing code?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    05-13-2008
    Posts
    10
    EDIT: I've managed to make my idea at the end of this post work, so far. If I get stuck, I'll post again. Thank you Richard and RoyUK for your time and help.



    I don't have anything in the way of "error checking code": my current code simply moves to the sheet to copy to, moves to the next available blank row and copies values across, then returns with a "quote success" message.

    I'd prefer something a little more in the way of checking individual cells, as quotes have 2 ways of being put through the factory, and these 2 ways have different machinery that could be used: I'll eventually (hopefully) have an if statement controlling whether one cell gets checked or the other depending on the route it will take.

    I also need guidance on how to skip the actual quoting part of the macro: I'm relatively new to visual basic. I'm considering using select case to control the walthrough of the macro, with it only executing the quoting code after all the checks have been made. This would help with the exiting of the macro, as it would mitigate using goto, and I'm more familiar with select case from working in C.

    However, I'm unsure as to the statement I should use: all the select case examples I've seen have been referring to conditions in a particular cell, rather than checking cells for a particular condition. In pseudo-code, it would be:

    Please Login or Register  to view this content.


    Is this possible (I don't know if you could set the select case as activecell.isblank or similar then set activecell = B3 etc.)?
    Last edited by DavidSmithson; 09-08-2008 at 11:29 AM. Reason: Found possible solution to problem stated

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Having the user specify a column when running a macro
    By garncor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-25-2008, 01:15 PM
  2. macro to copy cells to text files
    By docaia in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-28-2008, 03:30 AM
  3. Macro to Lock cells in protected sheet depending on criteria
    By Zyphon in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-30-2008, 12:00 PM
  4. running macro deletes non designated cells
    By chrismcclain077 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-22-2007, 08:54 PM
  5. Macro to hide columns based on contents of two cells
    By Korae13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2006, 12:40 AM

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