Do While only evaluating one of two clauses?

Hi,

I have a Do While loop with two clauses, but the 2nd one appears to not be evaluated properly, or i'm missing something trivial.

with the example below, if you put HI somewhere in a column, and then select a cell below it in the same column you should get "Found" in the immediate window. If you select a column without HI it will continue looking up the column until it error's when it trys to look at Row Zero.

Im a little confused as my clause to detect that error and stop it seems to be ignored.

Please Login or Register  to view this content.

Can anyone shed light on why this is happening?

A few things = the value for row is negative so source.row - row (postive minus a negative number) will always be greater than zero so

Please Login or Register  to view this content.

Otherwise you will try to get the value of a cell with a row number = 0
Secondly Source will never be Nothing so change

Please Login or Register  to view this content.

Your whole code should be

Please Login or Register  to view this content.

It is an around about way of doing what you are after (finding the word "Hi") in a column. Another way would be

Please Login or Register  to view this content.

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Yes Source should have been Result in the example, its correct in my working code, just wrote the example wrong

Negative numbers are no fun, I've been looking at that clause for a while today trying to figure out why, was a pretty simple mistake as I thought.

I'll look into changing the loops to Find()'s though as its probably a whole lot faster.

Do you know if its possible to explicitly look upward from the current cell? because there could be multiple instances of the things im looking for, and it does need to be the one closest to the selection.

Thanks for the help

I don't know if you can search upwards apart from iterating each cell in a range as you did in your previous code however you could find all the values with the folowing

Please Login or Register  to view this content.

Otherwise use you code as I posted in my previous post with the corrections so the first found cell will be the cell closest to the active cell.
Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Hi

Searching backwards can be done using the searchdirection parameter.

Please Login or Register  to view this content.

rylo

Thanks rylo - I was sure there would be with the searchDirection parameter. Learn something new everyday

That looks like it should do the trick!

Thanks a lot