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