Dropdown list, Macro

excelledsoftware · #5 10-22-2013, 06:34 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA: How to place dropdown list next to text	YigalB	Word VBA	0	08-11-2013 01:48 PM
block selection in dropdown list	Intruder	Excel	2	01-10-2013 10:20 AM
dropdown list for documents	r_p_t_0	Word	2	12-18-2012 05:55 AM
Can I have a dropdown list to choose from in mail merge?	lawlaw	Mail Merge	3	11-29-2012 04:47 PM
Dropdown list of email addresses	J Partridge	Outlook	1	01-13-2011 06:37 AM