+ Reply to Thread
Results 1 to 6 of 6

conditional check, action, then continue

  1. #1
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    69

    conditional check, action, then continue

    Hi there,

    My main goal here is to push a button, and based on the contents of a cell will either show or hide a worksheet.

    I have a macro that checks the contents of a variable on a worksheet for a #DIV/0! error, if there is one, performs an action, and if there isn't, performs an action.

    Please Login or Register  to view this content.
    My problem is I have a long list of these checks I wish to perform in one foul swoop, each with differing actions and reactions based on input. 30 in total. As I'm sure you can see, if there is a divisible by zero error I wish to hide the worksheet, and if there isn't, I wish to show it.

    Long story short, can anyone help with this macro without coding 30 different IF statements?



    Regards,
    Chris
    Regards,
    Xtopher

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: conditional check, action, then continue

    Sure, list all 30 tests in the order of importance and the sheets these tests should performed on.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    69

    Re: conditional check, action, then continue

    The variables are all on a sheet called Variables. The variables are as follows;

    GROUP1dayworkgangrate
    GROUP1dayshiftgangrate
    GROUP1nightshiftgangrate
    GROUP1svrdaygangrate
    GROUP1svrnightgangrate

    Repeat for Groups 2 to 6.

    Order of preference isn't important, but I I had to choose one I would say Group 1 and upwards.

    Thank you!
    Last edited by Xtopher; 06-11-2013 at 07:32 PM.

  4. #4
    Registered User
    Join Date
    04-05-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    69

    Re: conditional check, action, then continue

    I've just realised I didn't mention the sheets that are to be shown/hidden. They are from Sheets(11) through to (40).

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: conditional check, action, then continue

    Hi Xtopher,

    Uploading of sample workbook is better..

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: conditional check, action, then continue

    I recommend you highlight all the variable cells in a single group all at once and give them a global name for the whole group like GROUP1.
    Repeat for all the cell in Group2, etc.

    Now you can cycle through all the sheets and all the GROUPs on the sheets (1 through 6 for each sheet) and test the cells in each group for an error. If an error is ever detected, the sheet is hidden and the macro moves on to the next sheet in the workbook. Something like this:

    Please Login or Register  to view this content.

+ 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