Calculated Results for Series of Input Values

[GasGuy]

I am using a large spreadsheet that has one input value and returns one calculated value. I want to input a series of values (say 1 to 50) into a cell and get the calculated result for each value. How can I do this without typing each input value into the cell individually? The spreadsheet is too involved to put into a single column which could be copied across a sheet.

 

[ivymike]

If you've got one input value and one output value, but do a mess of calculations in between, it sounds like you could simplify things a bit by writing a function to do the calculations.

ex:

Function CalcMyVal(InVal As Single)
Dim TempVal As Single
TempVal = InVal * 5
TempVal = TempVal + 6
TempVal = TempVal / 400
CalcMyVal = TempVal
End Function

then you could have a column of inputs, and a column where the outputs were calculated:

| A | B
----------
1| 15 | =calcmyval(A1)
2| 5 | =calcmyval(A2)

Alternatively, you could write a macro to change the input cell, catch the output and store it somewhere, change the input again, and so on:

Sub inputswapper()
Dim count1 As Integer, TempVal As Single, InputVal As Single
For count1 = 1 To 50
'calculate the input value for your SS
InputVal = 30 * count1
'set the input cell
Sheets("sheet1&quot.Cells(1, 1) = InputVal
'read the output cell
TempVal = Sheets("sheet1&quot.Cells(5, 5).Value
' set up a table in sheet3 for the results
'put inputval in table
Sheets("sheet3&quot.Cells(count1 + 3, 1) = InputVal
'put corresponding output in table
Sheets("sheet3&quot.Cells(count1 + 3, 2) = TempVal
'increment and repeat the loop
Next count1
End Sub

 

[yakpol]

You will have to write a macro in VBA to accomplish this task. Suppose your variable is in cell D2 and the result in cell E2, Input values are in column A and output values are to be in column B. The routine copies the values from input column into the variable and then copies the result value into the output column.

Sub test()

Dim rngInput As Range, rngOutput As Range
Set rngInput = Range("A2")
Set rngOutput = Range("B2")

Do
    Range("D2").Value = rngInput.Value
    rngOutput.Value = Range("E2").Value
    Set rngInput = rngInput.Offset(1)
    Set rngOutput = rngOutput.Offset(1)
Loop Until IsEmpty(rngInput)
End Sub

 

[GasGuy]

Thanks guys, I've been avoiding learning VBA until now - looks like the time has come to get into it.

 

[nitin36537]

You can also use from the menu --- Data --> Table
This will solve your problem with out any VBA function requirement.

Nitin Patel
India