Looping Excel Formulas

I had written a rather complicated excel spreadsheet previously.
Now, I need to loop it.

I have attached the spreadsheet and it works like this
1) Input: I enter flow rate between 0.036 to 1.61 into the red box
2) The spreadsheet does some lengthy calculations
3) Output: The spreadsheet produces 2 output values in the yellow boxes - frequency and amplitude.

Now, I need to repeat this procedure many times for different inputs to plot an input vs. output graph.

I.e. I need to have inputs from 0.036 to 1.61 in steps of 0.001, and get the corresponding output.
In the attached "To Plot" worksheet is the sample result which I should get.

If it was C, the loop would go something like this.

int x = 0.036;
while (x < 1.61) {

//(read input, calculate, produce output, save to file)

x=x+0.001;
}


So how can I implement this? Thanks in advance for your replies!

Try this macro out, it should do what you are asking

Sub LoopInOut()
    For i = 3 To Sheets("To Plot").Range("A100000").End(xlUp).Row
        Sheets("GUI").Range("C10").Value = Sheets("To Plot").Cells(i, 1).Value
        Application.Calculate
        Sheets("To Plot").Cells(i, 2).Value = Sheets("GUI").Range("C15").Value
        Sheets("To Plot").Cells(i, 3).Value = Sheets("GUI").Range("C14").Value
    Next i
End Sub

if you specify an input sheet with values, i can modify to add the output in what ever format you are looking for.

Try this:

Sub LoopInOut()
    i = 4
    With Sheets("To Plot")
        .Range("A" & i - 1).Value = .Range("E5").Value
        Do Until .Range("A" & i - 1).Value >= .Range("F5").Value
           .Range("A" & i).Value = .Range("A" & i - 1).Value + .Range("G5").Value
           i = i + 1
        Loop
    
        For i = 3 To .Range("A100000").End(xlUp).Row
            Sheets("GUI").Range("C10").Value = .Cells(i, 1).Value
            Application.Calculate
            .Cells(i, 2).Value = Sheets("GUI").Range("C15").Value
            .Cells(i, 3).Value = Sheets("GUI").Range("C14").Value
        Next i
    End With
End Sub

Originally Posted by DGagnon

Try this:

Sub LoopInOut()
    i = 4
    With Sheets("To Plot")
        .Range("A" & i - 1).Value = .Range("E5").Value
        Do Until .Range("A" & i - 1).Value >= .Range("F5").Value
           .Range("A" & i).Value = .Range("A" & i - 1).Value + .Range("G5").Value
           i = i + 1
        Loop
    
        For i = 3 To .Range("A100000").End(xlUp).Row
            Sheets("GUI").Range("C10").Value = .Cells(i, 1).Value
            Application.Calculate
            .Cells(i, 2).Value = Sheets("GUI").Range("C15").Value
            .Cells(i, 3).Value = Sheets("GUI").Range("C14").Value
        Next i
    End With
End Sub

Thanks!
Works great except for one slight problem - it overshoots by one increment, i.e. stopping at 1.611 instead of 1.61

Edit: The last value, to be accurate is 1.61099999999993, so maybe it's due to some rounding problems?
The rounding problem seems to start between rows 1018 and 1019, with input values 1.051 and 1.05199999999999

i added rounding to the 4th digit here:

Sub LoopInOut()
    i = 4
    With Sheets("To Plot")
        .Range("A" & i - 1).Value = .Range("E5").Value
        Do Until .Range("A" & i - 1).Value >= .Range("F5").Value
           .Range("A" & i).Value = Round(.Range("A" & i - 1).Value + .Range("G5").Value, 4)
           i = i + 1
        Loop
    
        For i = 3 To .Range("A100000").End(xlUp).Row
            Sheets("GUI").Range("C10").Value = .Cells(i, 1).Value
            Application.Calculate
            .Cells(i, 2).Value = Sheets("GUI").Range("C15").Value
            .Cells(i, 3).Value = Sheets("GUI").Range("C14").Value
        Next i
    End With
End Sub

this should work for you

Sub LoopInOut()
    i = 3
    With Sheets("To Plot")
        .Range("A" & i).Value = .Range("E5").Value
        Do Until .Range("A" & i - 1).Value >= .Range("F5").Value And i > 3
            If i > 3 Then .Range("A" & i).Value = Round(.Range("A" & i - 1).Value + .Range("G5").Value, 4)
            Sheets("GUI").Range("C10").Value = .Cells(i, 1).Value
            Application.Calculate
            .Cells(i, 2).Value = Sheets("GUI").Range("C15").Value
            .Cells(i, 3).Value = Sheets("GUI").Range("C14").Value
            i = i + 1
        Loop
    End With
End Sub

Thank you, Glad I could help