Hi


In cell A1 I have a dropdown list using the validation list type. In cell E1 I have a True or False formula. For example if the values 10, 20 and 30 in the dropdown list correspond to True, I want the macro to tell the dropdown list to stop its run at the value 30 and not move to the value 40 which would correspond to False, Also how could I repeat the macro for the following rows?
Thanks

Not quite sure here. Let me inform you with what I understand so far.
Im assuming this means you have a validation list of 10, 20, 30, 40 etc. that you can select from.
Sounds like an if formula.
Already lost here. Cell A1 has a value from 10 - 90 or some other range Your if formula seems to check if the value in A1 is greater than or Less than another value yet this some how refers to true?
I would need to understand the if formula you are using from the first confusion point to go further.

Let me know.

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

Attached Files

Design.xlsx (28.3 KB, 19 views)

Ok the attachment helped substantially. I do believe that a do while loop will accomplish this. I can write one for you but wont be able to get to it till tomorrow but I will write some simple do loops for you and show you how they work so you can see how to do this.

Alright thanks for your patience with this. I have written a very simple loop to process this for you.

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

Hi
Thank you very much the loop worked perfectly, sorry for not responding earlier I was out of town the past few days.
I would greatly appreciate it if you could explain the loop like you suggested I would like to know how it works, as I would like to use the same method for other projects.
You are correct I limited it to 10 values from 10 to 100 because of the dropdown list, as I felt checking it for values from 1 to 100 will be imposable to do manually.
Thanks

Thanks for getting back to me and Bravo for asking how the code works. There are a lot of people that could care less and you will find yourself being able to write things like this with a little practice. I will address a couple of things with the posted code so you can
A- Understand some key terms and methods
B- Understand how the code works
C- Understand how to manipulate the code to do other things such as perform the calculation going from 1 to 100 rather than 10 to 100.


With all that said lets look at the code again and we will break down each part.

This is the beginning to most macros it stands for sub routine. The sub can be named whatever you want it to be except that it cannot start with number or special symbol and must be all one word.
the part where it says "code to try number from 10 - 100 blah blah" is actually a comment.
You can see this from the ' marking.
Placing the ' before anything in your VBA code turns it to a comment giving you the ability to make any notes or explanations.

Next Part

This part can get a little tricky but all it is doing is declaring or dimming variables. Variables are basically names that you can reference later in your code. In this macro we have 2 variables that will be integers otherwise known as basic numbers. We then took each of those integers and gave the the value of 10. This will be explained in the next notes.
We need to stop here for a second. DO is a loop. Basically it is a group of code that keeps looping until we tell it to stop. The reason DO is typed twice is because it is a loop within a loop. These are very confusing to write at first so it is better to write your inside loop and then write the outside loop around it. This will make more sense when we explain the loop a little more.
At this point it may look kind of overwhelming but just remember that the method "Range" allows you to select, copy, delete or otherwise modify a range of cells in Excel. Here is a simple example of the Range method. Range("a1").value = "Kitty Cat"
What that would do is put the text Kitty Cat in cell A1 of your worksheet
What this code does is a little bit different.
You will notice that the range is "aa" & CheckRow VBA sees this as AA10. why? because we declared CheckRow to be the value 10. Also notice that CheckRow is not in quotes but AA is. This is because if a text string is not in quotes VBA will look throughout the code to try and find out what it does. Meaning if you wrote range(AA & CheckRow).value VBA would say that AA is undefined because it would not be a variable. This is confusing at first but until you get it just follow the syntax of range till you are comfortable.

So with that long explanation the range method here selects AA10 again because the range is "AA" & the value of CheckRow equaling AA10. It gives the range AA10 the value of CheckValue which was declared as 10 as well up above. So range AA10 now equals 10

Remember how DO is a loop. This loop is going to keep on repeating so we need to make it change each time the loop is done. This little piece of code takes the value of CheckValue and adds 10 to it. The first time it goes through CheckValue is equal to 10 the 2nd time it is equal to 20 next time till 30 and so on until it gets to where the loop stops.

This is where the Inside loop is told to stop. It basically says keep adding 10 to CheckValue while the range CF10 = "Safe"
Again notice that Safe is in quotes so VBA does not think it is a variable. CF contains your IF formula and the loop keeps on going until it says UNSAFE.

But we do not want the value in CF to be UNSAFE we want it to go back to safe so what we need to do is kick the value back twice.
Why twice? reason is because the loop stops when the value in CF is UNSAFE but it still adds another 10 to CheckValue making it over by 20 so this piece of code takes the range of AA10 and gives the value of CheckValue - 20 thereby taking the CF range back to safe.

This is what an IF formula looks like in VBA. No Parentheses or commas just if and then
This IF statement only looks if the value is true, if we wanted it to look at a false possibility we would do an else but it was not necessary. The reason for the if statement is because while I was testing this code with your spreadsheet the values in AA would go beyond 100 before the value in CF would result in UNSAFE. Per your instructions you wanted it to go up to 100 and no higher. So all we needed to do was see if the value was over 100. If it was, take the value down to 100 otherwise leave it. Again we used the range method in the IF statement and then when the IF statement was done we typed End If. This is very important since VBA needs to know when the IF test and result are done.

A lot has been covered so far but remember how DO was typed twice. So far we have only ended the inside loop but the outside loop is still running. This piece of code modifies itself so it can loop for a certain amount of times. here is what it does.
CheckRow was equal to 10 but after this piece runs it will be equal to 11 so the INSIDE loop will start all over again but will be performing the same functions except on AA11 rather than AA10.
The next thing this code does is take the value of CheckValue back down to 10. Why? because CheckValue may be 30 or 50 or even 150 because it kept increasing by 10 until CF was unsafe. We need to get it back to 10 so it can try each increment in AA11 until CF is unsafe.
And the last part the end of the outside loop. At this point it will be checking cell AA11 but will stop once the checked cell = nothing. Remember the row in AA to be checked will increase by 1 each time the loop runs so it will stop once it gets to row 20 (I think it may be row 21 which ever = nothing)

The Final part of all basic subs end sub means the macro is done.

A couple of things to remember when dealing with loops. There is always the possibility of accidentally writing something called an infinite loop. This means the Loop NEVER STOPS. the code above could be an infinite loop with something like
Loop until range("aa" & CheckRow).value = 150

This would never end because as the code works the IF statement in the code takes the value of AA down to 100 so the code would keep going forever and never stop, even it it ran out of rows in Excel. The way to stop an infinite loop is by pressing CTRL + Break on your keyboard.

The other thing is that I mentioned you could modify this code to do the same thing with 1 to 100 rather than 10 to 100. All you need to do is change CheckValue to 1 when it is declared and when the inside loop starts. You also need to modify the lines that say CheckValue = CheckValue + 10 to
CheckValue = CheckValue + 1
Lastly in the if statement since we are dealing with 10's simply change that to - 2.

So the code would now look like this



Keep in mind this level of VBA is not suppose to be easy to comprehend at first but look at each piece and my explanations until it makes sense. If you are still having trouble understanding it let me know in a PM and we will discuss it further. I am completely self taught with all this and I know how difficult it can be to learn. I am here to help and I thank you for the opportunity to teach and learn from this exercise.

My apologies the code will not run just as fast since it will check in increments of 1. The regular code runs in less than a second but this method above takes about 3 seconds. Still pretty quick by I wanted to correct my inaccurate statement.