+ Reply to Thread
Results 1 to 8 of 8

Creating a Do Until loop

  1. 04-21-2010, 12:33 PM #1
    pld
    pld is offline
    Registered User
    Join Date
    04-20-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Creating a Do Until loop

    Please see the attached simplified version of my spreadsheet (Notes included). I'm desparately looking for a macro, the DO UNTIL and LOOP functions, which will take the cell marked in orange up in iterations 0.0001 and stop when the number in yellow is reached, in Column C.

    I've been looking at VBA for the past week and I'm affraid I'm none the wiser. Unfortunately the Solver does not work on my original spreadsheet because it is a non-smooth model.

    Thanks all.
    Attached Files Attached Files
    Last edited by pld; 04-21-2010 at 02:40 PM.
    Reply With QuoteRegister To Reply
  2. 04-21-2010, 01:18 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a Do Until loop

    Looks like some of your goal is missing, since you could simply copy the G9 value into F9 and be instantly done. So you're doing something in between each iteration, yes?

    Anyway:

    Please Login or Register  to view this content.
    _________________
    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!)
    Reply With QuoteRegister To Reply
  3. 04-21-2010, 01:38 PM #3
    pld
    pld is offline
    Registered User
    Join Date
    04-20-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a Do Until loop

    Well actually I simplified the example I posted because I just wanted to focus on the code. In reailty the C column derived from many variables which work independantly of one another. Since I'm still new to VBA, you're right, I will probably be able to expand on what you've given me as I learn more.

    What you have given me is great; thanks! I really do appreciate it!

    Is there any way I can add to the macro to stop when it reaches the yellow highlighted number, and what would really put the cherry on top, is if the macro would reset the orange cell to zero before each Run.

    Thanks again!
    Reply With QuoteRegister To Reply
  4. 04-21-2010, 01:43 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a Do Until loop

    If you're actually asking how to get Excel to search the whole sheet to "find" a yellow cell....ugh? How unintuitive is that? I'm sure you can concieve a better technique to ID the value to iterate to other than cell color, can't you? Why not just enter the value in G9 and move on?

    You can reset a value with:
    Please Login or Register  to view this content.

    Put that where you want. At the top?
    Reply With QuoteRegister To Reply
  5. 04-21-2010, 01:49 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a Do Until loop

    Ok, this will find the colored yellow cell, too. Heh...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 04-21-2010, 02:03 PM #6
    pld
    pld is offline
    Registered User
    Join Date
    04-20-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a Do Until loop

    Not if I was color blind. ha ha!

    Sorry if I didn't explain myself properly, my fault. What you gave me originally is great. But what I also want the code to do it to stop iterating when a number in Column C equals the number in the yellow cell. In this case the first number from Column C to equal the yellow highlighted number would be the highest number in Column C because I'm using a simple addition formula between Column B and the orange cell.

    In the main spreadsheet these numbers (Column C) will increase at different rates because of other variables involved. I'm just after the code at this stage.

    Once again, I do appreciate your help!
    Reply With QuoteRegister To Reply
  7. 04-21-2010, 02:21 PM #7
    pld
    pld is offline
    Registered User
    Join Date
    04-20-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a Do Until loop

    JBeaucaire,

    It's stopping perfectly, thanks!
    Reply With QuoteRegister To Reply
  8. 04-21-2010, 02:31 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a Do Until loop

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1