VBA: Do While Loop with multiple conditions

[rootengineering]

Hello Excel Forum,

I'm a VBA newbie, so please forgive any obvious errors.

I'm stuck in a Do While Loop. I have a user form where a First and Last Name can be entered. When the user clicks a button (Number Check), a worksheet is searched for a Row that contains the First Name (in column C), and the Last Name (in column D). When a row is found with the First and Last Name, a message box displays the row location. This works well if the both First and Last name exist in the spreadsheet. However, if both First and Last names do not exist in the spreadsheet, the program will get stuck in a loop, and i am forced to use Crtl+Pause to exit.


Here's what the spread sheet looks like:

And here's the VBA code:

Please Login or Register  to view this content.

This is the error I get: Run-time Error '91': Object variable or With block variable not set.
The code is getting hung up at this line:

Please Login or Register  to view this content.

Any suggestions you may have is greatly appreciated.

Thanks!

-Ryan

[6StringJazzer]

Welcome to the Forum rootengineering!

First let me thank you for the best-explained question I've seen on a user's first post. I really appreciate a great explanation.

You have several problems.

One is that your While condition is an Or and should be an And. See my update below. It will always be true that one of those conditions is true, so you will never exit the loop. You want to stay in the loop only as long as they are both true.

Second is that you need to check to see if rngFound came back as Nothing (i.e., the Find function didn't find anything). In fact you are seeing this error occur: rngFound is Nothing, so it cannot evaluate rngFound.Address. You will have to exit the loop immediately after the call to Find.

This is also why you need an If/ElseIf after your loop. If you try to check for rngFound Is Nothing and check rngFound.Row in the same expression, it will cause an error if rngFound is Nothing. VBA will evaluate the entire expression, whether it needs to or not.

I did not try to fix this but you have typos in your variable names (e.g., strLasttName, strLasName). I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

I can't test this without your file but if this doesn't work, feel free to attach the file and I'll troubleshoot.

Please Login or Register  to view this content.

The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[rootengineering]

Thanks 6StringJazzer!

I used your suggestions and added one more Else command after the first Exit Do. This seems to do the trick. Now i need to go read up on the Option Explicit feature.

Here's the revised code for future reference (without Option Explicit feature).

Please Login or Register  to view this content.

[6StringJazzer]

Glad it helped!

Now i need to go read up on the Option Explicit feature.

In my suggestion above, the reference to "declare variables" is a link to an article I wrote about it, that should explain everything.