VBA - If/And/Or/Else - MsgBox after 'Else' stuck in repeat trigger.

[Kalisary]

Hello,

Very novice VBA user here. Apologies for the clumsy name of this thread, I'm not quite sure how to explain what I'm experiencing.

I am attempting to implement a solution at work, whereby a summary sheet ('TravelTimeCalculator') is created that can call up all hours that need to be paid for a given staff member in a given month that is not included in a 'job', such as travel time and wait time between jobs. The spreadsheet has multiple sheets that need to be searched to pull this data.

Essentially, on this summary sheet I have a cell with predefined choices for the staff members name (B2) and another where the month can be selected (B3). The macro then searches all sheets for a row that has that staff members name, the selected month and is designated as 'travel' or 'approved wait time'. Where it finds such a row it is copied onto the summary sheet.

This much works fine with the following solution;

Please Login or Register  to view this content.

However, I would like it to be able to tell the user if no records match the set criteria, so we can differentiate between a true absence of match and a broken macro

To do this I attempted the following code;

Please Login or Register  to view this content.

This appears to work, to a point. If I put in a criteria I know cannot be matched, I get the appropriate pop up. However, on clicking 'okay', the pop triggers again immediately and appears to be stuck in an infinite loop, not allowing the user to do anything else. To get out of this, I have to force close excel.

I thought it might be that it was stuck looping between the worksheets. However, adding an 'End if' after 'Next WkSht' gives the error 'End if without block if'.

Does anyone have any ideas of how I could stop the MsgBox pop up loop, or any other way to signify that there are no rows matching the given criteria in the workbook?

Please do not hesitate to ask for additional explanation if I am not clear. I know am not at all knowledgeable about appropriate terms

Thank you very much for any assistance you can provide,

Courtney.

[Andrew-R]

Your MsgBox is within the loop going through the first 3,000 rows, so it will pop up for every row that doesn't match your criteria. So, it's not an infinite loop, it's just that you haven't pressed "OK" 3,000 times

There are more efficient ways of running your code, but you could try something like this:

Please Login or Register  to view this content.

[Kalisary]

Thank you, Andrew, that works a treat.

A 3,000 long loop, oh my.

I would appreciate any input into efficiency, if you have any interest or time to spare, I have noticed it runs a little slow. This solution was very much a mash-up of multiple solutions to similar problems posted on the internet.

Thank you again,

Courtney.

[Kyle123]

Can you post a sample of your workbook?

[Andrew-R]

Courtney,

Anything which has to loop through rows is likely to be slow. The usual way around this is to use the .Find method to find values you're looking for, as it saves you having to check every row in turn.

Something like this, maybe:

Please Login or Register  to view this content.