+ Reply to Thread
Results 1 to 5 of 5

How to Iterate random numbers until a different cell with a non-random value says "OK"

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    Costa Mesa, California
    MS-Off Ver
    Excel 2003
    Posts
    3

    How to Iterate random numbers until a different cell with a non-random value says "OK"

    I am very new to VBA. Here us my problem.

    I have four cells that include large random numbers. When those numbers have certain characteristics and relate appropriately to each other, another cell acknowledges that fact by displaying the number 1000.

    Say cell B1 is the cell that will display the number 1000.

    How can I write the code so that the spreadsheet will keep re-calculating until 1000 appears in B1 and then stop? I want a code that references only B1 and not the cells with random numbers. Is there a way to do this without VBA?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,611

    Re: How to Iterate random numbers until a different cell with a non-random value says "OK"

    One way:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-21-2012
    Location
    Costa Mesa, California
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How to Iterate random numbers until a different cell with a non-random value says "OK"

    I entered it in the VBA. How do I activate it without pressing the f9 key repeatedly? In "Options" I have it set to automatic and 10000 iterations. When I click on f9, it appears to recalculate once. It does not keep going until B1 registers 1000. Sorry I'm so ignorant about these things.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,611

    Re: How to Iterate random numbers until a different cell with a non-random value says "OK"

    Execution is unrelated to iterative calculation; the code iterates the calculation.

    Do Alt+F8 to bring up the Macros dialog, and run x.
    Last edited by shg; 11-21-2012 at 05:19 PM.

  5. #5
    Registered User
    Join Date
    11-21-2012
    Location
    Costa Mesa, California
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How to Iterate random numbers until a different cell with a non-random value says "OK"

    At first it kept calculating but I had referenced a cell that was empty rather than B1. After I referenced B1, I got the message
    Run-time error "13: Type mismatch

    The following line is highlighted
    Loop Until Range("B1").Value = 1000

    ++++++++++++++++++++++++++

    I discovered that the macro generates this error only in Excel 97

    In Excel 2003, It works perfectly. Thank you very much for your help shg. This will save me a great deal of time.
    Last edited by zxcvbnm; 11-22-2012 at 12:21 PM.

+ 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