VBA to perform an iteration

  • Hi all,

    I am trying to compile a VBA that performs an iteration of two assumed variables. Basically I need to assume two temperatures, perform a series of calculations and then I get two new temperatures. these new temperatures are then replaced in the location of the first assumed temperatures and the process is repeated until the difference between the assumed and calculated temperatures is minimal.

    I don't know how to write VBA from scratch, but I am able to compile based on Google searches of code. I have the following but I am running into an issue since the solution is not easily found. sometimes the assumed temperature leads the calculation to give a division by zero, or #NUM!, for that reason I get a type 13 mismatch. I need to include an error handler than changes the initial assumed temperature.

    This code works the first iteration, but fails with a type 13 on the second. AB9 and AB4 are the first assumed temperatures. AB17 and AB12 are the calculated temperatures. AB20 is the error difference between assumed and calculated.

    Appreciate the help.

  • Thanks for the reply Justin.

    I apologize as I cannot post the worksheet due to work rules.

    I will try to explain in details.

    Cell C28 is input by the user. Lets say it has a value of 100.

    Line 9 of the code will subtract " i " from 100 = 99.9. This is my first assumed value of AB9 for the first iteration.

    Line 11 will divide AB9 by 2 = 49.95. This is my first assumed value of AB4 for the first iteration.

    The worksheet can now solve itself using these values and generate values from formulas that are placed in cells AB17 and AB12.

    These newly generated values in AB17 and AB12 are replaced in place of AB9 and AB4, respectively, and are the new assumed values of the second iteration (Line 15-24 of the code).

    This process in looped until the error criteria is met which is placed in cell AB20 = ABS( (AB17-AB9) + (AB12-AB4)). Basically i want to match AB17 with AB9 and AB12 with AB4. This way the solution would have converged.

    Now being that the formulas are complicated, sometimes they evaluate a #NUM! or #DIV/0! resulting in a type 13 mismatch error. What I want is whenever this error occurs, it will go to the error handler which adds 0.1 to i = 0.2 and go back to the beginning in which new values of AB9 and AB4 are placed.

    Line 9 of the code will subtract " i + 0.1 " from 100 = 99.8. i + 0.1 due to the error handler code.

    Line 11 will divide AB9 by 2 = 49.9.

    and the whole process is repeated until AB20 is satisfied.

    I hope this makes it clearer. Thanks for the help.

  • It is a bit difficult to work out without being able to reproduce the error.

    I think this code replicates yours but should run a little faster.

    to catch the error occurring in a cell use the iferror function, something along the lines of:

    If Application.WorksheetFunction.IfError(Target, True) = True Then i = i + 0.1

    where you replace "Target" with the cell with the error (eg ws.range("A1") etc.)

    Again without being able to test your sheet or an example it is difficult to work out.

    HTH, good luck

  • Thanks for the input Justin.

    After many trials, if found out that error is occurring on this code line "Do Until Range("AB20").Value < 0.0001".

    Whenever AB20 is evaluated as #NUM!, the macro stops and gives a type 13 error.

    Is there anything I can add to this line that whenever it evaluates a #NUM! it goes to the error handler?

    I tried "If Application.WorksheetFunction.IfError(ws.Range("AB20"), True) = True Then i = i + 0.1" but it doesn't seem to work.


  • Sorry TH, that code works for me to catch the error of a formula returning an error in a cell due to a divide by zero. It would probably result in a relatively quick solution if you could post an example sheet with the values and the code working as it does on your copy. Without being able to replicate the error you have it is difficult to trouble shoot.

    If you cannot provide an example sheet I suggest you request this thread be deleted and start one specifically requesting a solution to error catching a cell returning a divide by zero error. I.e. re-title your thread and be more specific in your requirement.

    Maybe one of the mods can provide better insight.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!