Thanks:  0
Likes:  0

# Thread: Act like I'm changing a value of a cell

1. ## Act like I'm changing a value of a cell

I have a formula with an input and an output but I don't want to manually enter the input multiple times then see what the output it and manually enter it into another cell. Is their a simple way to do this?

2. ## Re: Act like I'm changing a value of a cell

you need to explain what you want to do and expect to see, give examples, tell use where inputs and outputs occur

3. ## Re: Act like I'm changing a value of a cell

Sorry, here is my scenario. I want the D36 cell to be my actual shock length when frame height is at the height of C36. Right now I must manually change cell D7 and see what cell F33 actually is. Then I have to manually enter that value into cell C36. Does this help?

4. ## Re: Act like I'm changing a value of a cell

are the variations constant, or could they be calculated on the fly (is z always 0,497 of x eg) , if stock I would be looking at LOOKUP as an array, or using VLOOKUP against a set of fixed values

A much better explanation than your first offering which didn't give me anything to think about

what is the calculation between D7 to achieve F33

knowing the calculation would help

5. ## Re: Act like I'm changing a value of a cell

Originally Posted by mole999
are the variations constant, or could they be calculated on the fly (is z always 0,497 of x eg) , if stock I would be looking at LOOKUP as an array, or using VLOOKUP against a set of fixed values

A much better explanation than your first offering which didn't give me anything to think about

what is the calculation between D7 to achieve F33

knowing the calculation would help
The problem with this is that the shock length is changing at a different rate than what frame height is. Which is the whole point behind this program. The calculations between D7 and F33 is pretty extensive. It uses all of the yellow modifiable inputs to come up with the shock length. That's why I was hoping there is any easier way of approaching this. I don't know anything about Arrays and I looked into Vlookup but didn't understand how it would work in this application.

6. ## Re: Act like I'm changing a value of a cell

whoever constructs the formula, needs to understand the interplay between your variables, so starting at 12 what makes 17.454 your final value ?

7. ## Re: Act like I'm changing a value of a cell

Originally Posted by mole999
whoever constructs the formula, needs to understand the interplay between your variables, so starting at 12 what makes 17.454 your final value ?
There are about 30 different calculations to come up with the 17.454 including many trig functions and finding the connection of 2 circles to get to the end result. You can't put all of these calculations into 1 long trig function. This is the problem!

8. ## Re: Act like I'm changing a value of a cell

you achieve it manually ?, then it must be possible to programme it. One option is to establish helper cells that do part of the work before the next stage

9. ## Re: Act like I'm changing a value of a cell

Yes I can achieve it manually by entering the frame height value in D7 then it tells me shock length in F33. I have no idea what you mean in the last post.

10. ## Re: Act like I'm changing a value of a cell

I think that what Mole is after is that the steps that the spreadsheet does to calculate the shock length to the frame height can be coded into a VBA UDF.

But given your current set-up this Macro should fill in the chart thats at the bottom of your data set.
You'll want to make the "Sheet1" match your sheet name and you might want to fiddle with the range through which X goes.

Code:
```Sub test()
Dim X as Double
Dim myCell as Range

With Sheets("Sheet1")
Set myCell = .Range("C36")

For X = 15 To 3 Step -.025
myCell.Value = x
.Range("D7").Value = x
Calculate
myCell.Offset(0,1).Value = .Range("F33").Value
Set MyCell = myCell.Offset(1, 0)
Next X
End With
End Sub```
Actualy if you are going to be doing this regularly, you could use VBA to create a lookup chart of values.
Then rather than going through the D7/F33 routine you could either look up the value from the chart or interpolate for intermediate values (I don't me you personally, I mean that spread sheet formulas can do that, while they can't do the D7/F333)

Are there any other variable besides D7? or is this the shocklenght for a particular frame of a particular construction and if that changes the result will change.

If F33 is purely a function of one variable (D77) it would probably be worth your time to code it in a VBA UDF rather that go through the worksheet.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•