Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Trestala

Mechanical
Feb 15, 2015
133
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?
 
Replies continue below

Recommended for you

Have you looked at the "What-if" analysis options?
 
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:



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

Part and Inventory Search

Sponsor