Consulting

Results 1 to 2 of 2

Thread: Possible to Run Goal Seek on Array elements Within VBA?

  1. #1

    Possible to Run Goal Seek on Array elements Within VBA?

    Is it Possible to Run Goal Seek on array elements within VBA, instead of on an Excel sheet range?

    I have a Sub that makes an array which takes initial values from an excel input sheet, does many calculations, and prints values back into a report region on an excel sheet; the output is roughly 200 rows x 28 columns of calculated values. I used to do this with formulas in every cell on the sheet, but it was of course very, very slow. I converted to an all-vba Sub that does the calculations quickly and pastes the finished values into the report range in excel.

    The problem is that I also need to run Goal Seek on various cells from time to time in order to make use of the model and Excel can't do it on a sheet cell that just has a value in it, it needs a cell with a formula. With my fancy, efficient array, I can't goal seek anymore because the cells are just numbers pasted in by my Sub!!!!!
    Is there a way to run some version of Goal Seek NOT on excel sheet ranges but on array members, like on MyArray(107,23) by testing an input value that is actually on the excel sheet, like Range("B2")? What would that code look like? The ultimate goal would be to call a macro just like calling Goal Seek, enter ranges for Set Cell, To Value, and By Changing Cell just like Excel Goal Seek does, and have it run the operation on MyArray, but any help at all would be most welcome.

  2. #2
    The method MS uses is pretty simplistic:

    https://support.microsoft.com/en-us/...ind-a-solution


    So to start with, I guess you would need

    1. A function to perform the calculation, e.g. f(x) = 123*x^2
    2. The desired 'goal', e.g. 10,000
    3. A sub that would guess a starting x, try it in g(x), and adjust until it closes to the 'goal' +/- a tolerance OR so many iterations have passed, and a place to put the x value that give an x such that 0 = f(x) - 10,000


    However there are other numerical methods like Newton Raphson method (that are faster) to iterate for a solution, that is difficult/ not possible to solve directly

    https://en.wikipedia.org/wiki/Newton's_method

    The attachment is a workbook with Newton-Raphson VBA function in it. The initial guess does not seem to be too sensitive, but you do need your function(s) written in VBA, and probably a different NR goal seek function of each of those since the NR function calls your f(x) internally
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Last edited by Paul_Hossler; 02-27-2017 at 07:50 AM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •