Macro or VB to Subtract from a cell until criterias are met.

[Arle8907]

Hello, I have a sheet that calculates the maximum load a beam can hold for a given span, and I am working on populating a table with the values. The cells i can change are the beam size, span, and max load. Based on those inputs the sheet calculates four different failure modes, and displays a pass/fail output for each. What i am hoping to do is manually input the beam size and span, then have excel start at some large number and subtract 0.1 from the number until all four of the pass/fail outputs show pass.

Thank you for your time.

 

[rlv01]

Why not use the GoalSeek feature?

 

[Arle8907]

Why not use the GoalSeek feature?

I tried that but i think since my criteria aren't formulas it can't find the slope of the equation to determine the answer. It looks like it bounces around randomly until it eventually hits the iteration limit.

 

[rlv01]

since my criteria aren't formulas

There must be formulas somewhere otherwise your plan to subtract 0.1 until you get a good result (which is iteration) is not going to work either.

 

[Arle8907]

There must be formulas somewhere otherwise your plan to subtract 0.1 until you get a good result (which is iteration) is not going to work either.

With the way this spreadsheet is set up unfortunately there is not without significant rework of the spreadsheet. With the work around i have right now it would take me longer to rework/develop a new sheet than it would to guess and check each value manually.

If cells A1, A2, A3, & A4 contain the true/false statements
cell A5=A1+A2+A3+A4 then since True=1 and false=0 if A5=4 all the conditions are true.
There is no way for excel to say If A5<>4 then *Max Load*-0.1 and check again?

Thank you for the help

 

[rlv01]

With the way this spreadsheet is set up unfortunately there is not without significant rework of the spreadsheet. With the work around i have right now it would take me longer to rework/develop a new sheet than it would to guess and check each value manually.

I'm not sure that is true. It is possible to goal seek a True/False output as long as you understand the formula that produced the True/False. One simple example

Book1
	A	B	C	D	E	F	G	H
1		Input		Fail Mode	Mode Status	Fail Calc	Sum	Combined Pass/Fail
2	Beam Size	10		1	Pass	10	40	Pass
3	Span	44		2	Pass	10
4	Max Load	779.2		3	Pass	10
5				4	Pass	10
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(F2=10,"Pass","Fail")
F2	F2	=IF($B$2+$B$3*$B$4>30000,10,0)
G2	G2	=SUM(F2:F5)
H2	H2	=IF(G2=40,"Pass","Fail")
F3	F3	=IF($B$2*$B$3*$B$4>15000,10,0)
F4	F4	=IF($B$2+$B$3*$B$4*2<1500000,10,0)
F5	F5	=IF($B$2*$B$3*$B$4*3>110000,10,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2,E2:E5	Cell Value	="Fail"	text	NO
H2,E2:E5	Cell Value	="Pass"	text	NO

Sub Macro1()
'
' Macro1 Macro
'
    Range("G2").GoalSeek Goal:=40, ChangingCell:=Range("B4")
End Sub

 

[rlv01]

If cells A1, A2, A3, & A4 contain the true/false statements
cell A5=A1+A2+A3+A4 then since True=1 and false=0 if A5=4 all the conditions are true.
There is no way for excel to say If A5<>4 then *Max Load*-0.1 and check again?

One way.

Sub TestAndCheck()
    Dim MaxLoad As Double
    Dim FoundResult As Boolean

    With ActiveSheet
        MaxLoad = .Range("E2").Value
        Do While MaxLoad > 0
            FoundResult = .Range("A5").Value
            If FoundResult Then
                Exit Do
            End If
            MaxLoad = Round(MaxLoad - 0.1, 1)
            .Range("E2").Value = MaxLoad
        Loop
    End With
    If Not FoundResult Then
        MsgBox "No Convergence", vbOKOnly Or vbCritical, "Failure to Converge"
    End If
End Sub

Book1
	A	B	C	D	E
1	FALSE		Beam Size	Span	Max Load
2	FALSE		10	44	800
3	TRUE
4	FALSE
5	FALSE
Sheet2

Cell Formulas
Range	Formula
A1	A1	=$E$2<= 500
A2	A2	=$E$2<= 700
A3	A3	=$E$2<= 900
A4	A4	=$E$2<= 399
A5	A5	=AND(A1:A4)