Calculated Results for Series of Input Values
Calculated Results for Series of Input Values
(OP)
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.





RE: Calculated Results for Series of Input Values
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").Cells(1, 1) = InputVal
'read the output cell
TempVal = Sheets("sheet1").Cells(5, 5).Value
' set up a table in sheet3 for the results
'put inputval in table
Sheets("sheet3").Cells(count1 + 3, 1) = InputVal
'put corresponding output in table
Sheets("sheet3").Cells(count1 + 3, 2) = TempVal
'increment and repeat the loop
Next count1
End Sub
RE: Calculated Results for Series of Input Values
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
RE: Calculated Results for Series of Input Values
RE: Calculated Results for Series of Input Values
This will solve your problem with out any VBA function requirement.
Nitin Patel
India