Quote:
Originally Posted by shield5
Hi
Attached the worksheet I am working on basically it is an engineering design problem:
1- In cell AA I have a dropdown list with values going from 10, 20, 30, and up to 100.
2- In cell CE I have a value as long as this value is less than 1 then the design is safe.
3- In cell CF I have a what if formula [=IF(CE10<=1,"Safe","Unsafe")] if the value in cell CE is less than 1 then the design is safe if it is more than 1 than the design is unsafe.
What I want the macro to do is go through the values in the dropdown list in AA one by one as long as the formula in CF states it is safe then it is ok, however if it returns with an answer unsafe than that the wrong answer. I want the macro to stop at the last value in the dropdown list that will give the answer safe. For example subjecting a member to a force 50 is safe however if it is subjected to a force 60 it will become unsafe. Basically I want the answer that will give me the max force a member can take before becoming unsafe.
Thanks
|
Alright thanks for your patience with this. I have written a very simple loop to process this for you.
Code:
Sub BeSafe()
'code to try number from 10 - 100 until an unsafe calculation is performed
Dim CheckCell As Integer, CheckValue As Integer
CheckRow = 10
CheckValue = 10
Do
Do
Range("aa" & CheckRow).Select
Range("aa" & CheckRow).Value = CheckValue
CheckValue = CheckValue + 10
Loop While Range("cf" & CheckRow).Value = "Safe"
' Take the value back to safe
Range("aa" & CheckRow).Value = CheckValue - 20
' Check if value is above 100
If Range("aa" & CheckRow).Value > 100 Then
Range("aa" & CheckRow).Value = 100
End If
CheckRow = CheckRow + 1
CheckValue = 10
Loop Until Range("aa" & CheckRow).Value = ""
End Sub
Now you are more than welcome to take this macro paste it in your editor, run it and be done with this project but you will probably find it more helpful to understand what is going on with it.
I will not force this knowledge upon you but will offer to explain it in a future post if you wish.
By the way this code should work for multiple rows if you add more. We can also have the code write the if statement in CF so you dont have to copy down the formulas but this should work for now.
Let me know how it works for you.
Oh one more thing. I get the feeling you were doing values 1 - 10 in column AA because it was a dropdown list. If you want to make this macro do the same but check with any value from 1 to 100 it is very easy to modify the code to do this and it will run just as fast.
Let us know
Thanks