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.

Sub Iteration()

Dim i As Single

i = 0.1

Do
    Range("AB9").Select
    ActiveCell.Formula = "=C28 - " & i & ""
    Range("AB4").Select
    ActiveCell.Formula = "=AB9 /2"

On Error GoTo ErrorHandler:
    Do Until Range("AB20").Value < 0.0001
        Range("AB17").Select
        Selection.Copy
        Range("AB9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("AB12").Select
        Selection.Copy
        Range("AB4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    Loop
ErrorHandler:
i = i + 0.1
Loop
End Sub

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.

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.

Thanks.