Looping on a single cell until I reach approximately 100% stress ratio

[Trestala]

Hello, I have an excel calculation sheet that calculates the required thickness under external pressure of a pressure vessel using ASME BPVC Div. 1, UG-28(c).

The calculation method is that you have to assume first a thickness to get some parameters in the Code based on the diameter-to-thickness ratio and length-to-outside diameter ratio to ultimately get the maximum allowable external pressure (MAEWP). This MAEWP can just be compare that value to the design external pressure (e.g. FV or -15psi.g). The design external pressure can be divided by the MAEWP to see if it is less than unity 1.0 so it passes the calculation.

So, I have a cell where I place my assumed initial thickness (e.g. cell A1) which then calculates the MAEWP (placed in other parts of the Sheet), then another cell (e.g. cell A2) where it shows the ratio between Design External Pressure and MAEWP.

I want to automatically increment the value in cell A1 (loop in VBA?), for example increments of 0.01mm starting from a minimum thickness (e.g. 3mm), until the ratio in A2 reaches close to 100% but would not exceed 100%. There would be times that A2 would be in error since there is range of Do/t and L/Do that is outside the Code tables.

Can anyone point me in the right direction on how I can go about this?

 

[3DDave]

Have you looked at the "What-if" analysis options?

 

[IRstuff]

Excel also has a Solver that you can goal seek a cell value based on changing one or more other cells. I used that feature quite a bit for work

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

Following on from the previous answers:

The goal-seek function will do what you want. It is under the "What-if analysis" icon on the Data Tab.

If you want more control and more options, or if you need to adjust more than one cell, the solver tool may be worth looking at. It is also on the Data tab, but may need to be activated, using "Excel Add-ins" on the Developer tab.

For information on how goal-seek can be automated using VBA, see:

https://newtonexcelbach.com/2009/07/25/using-goal-seek-on-multiple-cells/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Trestala]

Thank you all. I wasn't aware of the "What-if Analysis" option on Excel. It works great for this purpose. Thank you!