+ Reply to Thread
Results 1 to 7 of 7

reduce cell value by increment until condition is met

Hybrid View

MrVdc reduce cell value by... 08-24-2022, 10:27 AM
alansidman Re: reduce cell value by... 08-24-2022, 10:59 AM
MrVdc Re: reduce cell value by... 08-24-2022, 11:34 AM
MrShorty Re: reduce cell value by... 08-24-2022, 12:06 PM
MrVdc Re: reduce cell value by... 08-24-2022, 12:25 PM
MrVdc Re: reduce cell value by... 08-24-2022, 05:00 PM
alansidman Re: reduce cell value by... 08-24-2022, 07:07 PM
  1. #1
    Registered User
    Join Date
    08-24-2022
    Location
    London
    MS-Off Ver
    365 64bit
    Posts
    4

    reduce cell value by increment until condition is met

    Hi, I have driven myself "loopy" over something quite basic:

    "A1" is a variable integer that feeds into an equation that yields "b2" as the result. "B3" is the copied value of "b2" at the start, so "b3" = "b2". "B3" does not get refreshed if "B2" changes.

    The VBA code I am trying to compile will cover:
    A1 can only be between 0 and 1000.
    reduce the value of "a1" by incremental steps of 1 until b2 <> b3, then print the last "a1" value in "a1" where "b3"= "b2"


    every time i try this i get a plethora of errors or end up in an infinity loop
    any help would be greatly appreciated.
    many thanks

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,755

    Re: reduce cell value by increment until condition is met

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-24-2022
    Location
    London
    MS-Off Ver
    365 64bit
    Posts
    4

    Re: reduce cell value by increment until condition is met

    As requested

    in the attachment A3 is the variable i want incrementally lowered. J4 should equal J5.

    thanks for your help.

    Full disclosure, I uploaded a similar thread on Mrexcel https://www.mrexcel.com/board/thread...s-met.1214606/

    kind regards
    Attached Files Attached Files
    Last edited by alansidman; 08-24-2022 at 11:43 AM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,454

    Re: reduce cell value by increment until condition is met

    Looking at the VBA code and the spreadsheet (they are using different references, so that complicates things a bit), I see a couple of things:

    1) Both J4 and J5 contain a formula, and appear to always sum the same range, so these will always be the same. Could this be the source of your infinite loops?
    2) In order to understand the behavior of J4, I set up a data table to see the result of J4 for different inputs of A3: (https://www.excel-easy.com/examples/data-tables.html )
    2a) I enter 426 into M13. In M14, I enter =M13-1 and copy down as far as I like. In N12, I enter =J4.
    2b) Select M12:Nwhatever and insert a data table (Data -> What if -> Data Table). Tell it to use A3 as the row input cell. Finish and the data table fills with results.
    2c) Scanning down the results, I see that J4 remains constant at 29.01 until A3=85 when it becomes -21.14. Is this the value you are trying to find? If so, I just found it without any VBA at all.
    3) Assuming that 85 is the result you are trying to find, are you required to use this brute force subtract 1 from A3 until J4 changes algorithm? Is the behavior of J4 in this example (J4 is constant until the target value is reached, then never returns the original value again) representative of all of the problems, or will J4 behave differently for different scenarios? If this is the expected behavior of all problems and you are not required to use a brute force algorithm, something like a bisection algorithm should be able to solve this a lot more efficiently.

    I'm not entirely sure I understand all of what you are trying to do, but see if those comments help.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    08-24-2022
    Location
    London
    MS-Off Ver
    365 64bit
    Posts
    4

    Re: reduce cell value by increment until condition is met

    Dear Mr Shorty, thank you for your expedient response.

    item 1) J4 is supposed to be a paste value of J5, i must have hastilly hit paste when loading up the sample. J4 is not supposed to contain a formular.
    item 2) noted, however this is part of a 500 itteration calculation utilising solver programme output on an enormous data set, the vast majority I have not uploaded so I am really looking for an automated VBA solution. I could just run solver again making "a3" the minimum objective with constraints that J4 = j5 and the only variable being "a3", but the solver takes so long to compile, it is adding 15mins onto each itteration.
    item 3) solver has given me "an" optimum solution for J5, but not an optimum (min) for teh variables. I am just trying to reduce the variable numbers down to their minimum without changing the solver value. I figured a simple minus 1 increment until the value changed would be a processor friendly way of achieving the same outcome.

  6. #6
    Registered User
    Join Date
    08-24-2022
    Location
    London
    MS-Off Ver
    365 64bit
    Posts
    4

    Re: reduce cell value by increment until condition is met

    I have resolved it

    Do While Range("j4").Value = Range("j5").Value
    
    Range("a2").Value = Range("a2").Value - 1
    
    Loop
    
    Range("a2").Value = Range("a2").Value + 1
    
    End Sub
    Last edited by alansidman; 08-24-2022 at 07:07 PM.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,755

    Re: reduce cell value by increment until condition is met

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

    However, if you continue to not use code tags, you can expect to have your thread BLOCKED until you add them yourself.

+ 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. Formula to increment cell integer until comparative condition is met
    By ck42 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-11-2019, 09:56 AM
  2. Replies: 1
    Last Post: 04-02-2017, 10:18 AM
  3. Replies: 1
    Last Post: 12-17-2013, 03:18 PM
  4. [SOLVED] Control Button to increment by 1 and then select next cell in row to increment that cell
    By rammergu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2012, 07:35 PM
  5. Excel 2007 : Reduce count if condition met
    By bweber in forum Excel General
    Replies: 8
    Last Post: 04-04-2012, 03:39 PM
  6. Add a time increment when a condition is met.
    By RunHard in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2009, 06:52 AM
  7. Replies: 2
    Last Post: 08-22-2007, 10:51 AM

Tags for this Thread

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