×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculated Results for Series of Input Values

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

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").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

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
  

RE: Calculated Results for Series of Input Values

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

RE: Calculated Results for Series of Input Values

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

Nitin Patel
India

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources