Exit Row search code from MsgBox

[Frankie_The_Flyer]

Hi

I've a code that searches down a column and picks out rows where values meeting certain criteria are anomalies. Each anomaly brings up a Message box that identifies the problem. The message Box has yes/no buttons to allow the user to either exit the sub and fix the issue or continue with the check of the column.

Running through the code "manually" using F8 the

Please Login or Register  to view this content.

is seen by the code, but clicking on "No" doesn't exit the sub.

If I separate the code after "Then" and put the Exit Sub on the next line down, the code throws up "Next with no For" when it reaches the line "Next cll".

If I move the

Please Login or Register  to view this content.

outside of the "If cll.Value" lines of code it gets ignored on a manual run-through.

What do I need to do to get the MsgBox "No" button to stop the code and the "Yes" button to let the code continue please?

Here's the code

Please Login or Register  to view this content.

Example spreadsheet with code is attached.

Many thanks

Frankie

[HaHoBe]

Hi, Frankie,

there is no relation between the outcome of the MessageBox and response so the code canīt end.

From what I know the code should not be placed begind a sheet but inside a standard module:

Please Login or Register  to view this content.

Ciao,
Holger

[HaHoBe]

Hi Frankie,

itīs not a big deal but you should consider a Select Case against your Ifs. Every cell value will be checked with all Ifs no matter what value is shown while the Select Case will pass the cases and if a case is true the rest of them will be omitted. By checking for the contents to be equal to 2 first the code may look like this:

Please Login or Register  to view this content.

Ciao,
Holger

[torachan]

declare - Dim intResponse as vbMsgBoxResult not as Integer

[torachan]

change your LastRow search it does not appear to find anything

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Frankie_The_Flyer]

Thanks for sorting my code out HaHoBe. Much appreciated.
Even more appreciated is the improved code you've supplied!!
Thanks!

[Frankie_The_Flyer]

Hoi Torachan

LastRow is used to find a place down a column that is within the overall much larger column length. I probably shouldn't have used the wording "LastRow" as that bit of code equals a variable number on (another) sheet in the actual spreadsheet I'm working with. The next line of code ("RangeEnd") then adds the column ident to the number. So if LastRow = 63 then RangeEnd would = BZ63, giving a range (rng) of BZ5:BZ63.

I'm sure there's a better way of doing it but I was quite proud that I'd made it work!!

But thanks for the input. Much appreciated.

Frankie