+ Reply to Thread
Results 1 to 9 of 9

Clear Cell Range If formula result in A1 is > 0

  1. #1
    Forum Contributor
    Join Date
    01-25-2014
    Location
    Italy - Currently In USA
    MS-Off Ver
    Excel 2019
    Posts
    205

    Clear Cell Range If formula result in A1 is > 0

    Hello,
    I have two codes that works well on shee1 but I would like the same to occur on Sheet2 and Sheet3

    Example:

    On sheet1 (named - Present MA) I have a module (the timer) Code enter a value of 1 in cell A1 , and a code in the sheet1 that clears the row from B1 to U1.
    and same applies for each rows that receives value of 1 in column A.

    On shee2 (named - Past MA) I have a formula in A1 (=Sheet1!A1) and I would like for sheet2 row B1 to U1 to be cleared as well when the formula result is 1
    And Same should apply on sheet3 (named - Future MA)

    Any help would be appreciated
    Thank you
    cheers
    Nino

    Code in Module:
    Please Login or Register  to view this content.
    Code in Sheet1:

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Clear Cell Range If formula result in A1 is > 0

    A Worksheet_Change event in Sheet1 will work properly. However, it won't work properly in Sheet2 and Sheet3 because a Worksheet_Change event is not triggered by a change in a cell that is the result of a formula. For Sheet2 and Sheet3 you'll need to use a Worksheet_Calculate event. This is triggered when a formula on the sheet is calculated.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    01-25-2014
    Location
    Italy - Currently In USA
    MS-Off Ver
    Excel 2019
    Posts
    205

    Re: Clear Cell Range If formula result in A1 is > 0

    Hello Mumps1,
    Thank you for your fast help
    I tried to copy your code in Sheet 2 and Sheet3 but I get a debug window.
    Am I doing it wrong?
    I have attached a sample of the workbook to simplify the reasons
    If you Press start Timer you will see
    Thank you
    Nino
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Clear Cell Range If formula result in A1 is > 0

    I deleted Sheet2 and Sheet3 and when I press Start Timer, nothing happens on Sheet1. I don't quite follow what you are trying to achieve. Why do you need a timer? Can you explain in words what you want to do, step-by-step? I can't promise a solution but I'll have another look at it.

  5. #5
    Forum Contributor
    Join Date
    01-25-2014
    Location
    Italy - Currently In USA
    MS-Off Ver
    Excel 2019
    Posts
    205

    Re: Clear Cell Range If formula result in A1 is > 0

    Basically sheet2 and 3 have different formulas that are in same cells as sheet1
    each sheet will produce a different result in each row based on external inputs. those results end-up on a separate workbook as values before each row is cleared..
    The timer is so I don't have to enter the value 1 manually. because the actual workbook is 5000 rows long.
    Hence, the timer.
    As the timer advances down the rows and clearing the rows, each following row updates its results. Naturally excluding the rows that have been cleared.
    So this is all working fine on sheet1 but I would like sheet2 and sheet3 to do the same as sheet1.
    So by pacing a formula in column A of sheet2 and 3 (=Sheet1!A1) I wanted that to trigger the clear rows on sheet2 and 3 as well.

    Hope I was able to explain
    Thank you
    Nino

  6. #6
    Forum Contributor
    Join Date
    01-25-2014
    Location
    Italy - Currently In USA
    MS-Off Ver
    Excel 2019
    Posts
    205

    Re: Clear Cell Range If formula result in A1 is > 0

    I have reattached the sample workbook, the one you received earlier had your suggested code in it and I wasn't able to make it work.
    This workbook sheet1 works great (even if you delete Sheet 2 and 3)
    But i am unable to clear rows on sheet2 and 3.

    Cheers
    Attached Files Attached Files

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Clear Cell Range If formula result in A1 is > 0

    Try the attached file.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-25-2014
    Location
    Italy - Currently In USA
    MS-Off Ver
    Excel 2019
    Posts
    205

    Re: Clear Cell Range If formula result in A1 is > 0

    Brilliant, just Brilliant Mumps1.
    Works effortlessly.
    Thank you so much for your help
    I hope others will find this useful as well.
    Very happy
    Have a great day
    Cheers
    Nino

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Clear Cell Range If formula result in A1 is > 0

    You are very welcome.

+ 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. VBA to Clear 1 Cell and Clear other cells Formula only
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2017, 12:56 PM
  2. Clear Cell range upon Changing data in difference Range
    By snuffnchess in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2016, 02:22 AM
  3. Replies: 7
    Last Post: 11-27-2015, 11:12 AM
  4. [SOLVED] once cell is cleared the totals result from code wont clear
    By lengwer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2013, 02:33 PM
  5. Replies: 1
    Last Post: 06-21-2013, 04:05 AM
  6. [SOLVED] showing message box if the result of texbox doesn't match with the range then clear texbox
    By hamidrezaxy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2013, 01:04 PM
  7. Macro to insert formula result into range with zero values in cell
    By JPS in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-06-2005, 01:05 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