Logic Error - Code Is Looping Back

Hi all,

Using Excel 2013.

Code below:

• Prompt user to select cell on a worksheet.
• If worksheet does not contain data, ask user if wants to try another worksheet, if so try again, else exit.
• If worksheet does contain data, the true used range is output perfectly.

I'm testing beginning on a worksheet on a worksheet with no data.
I am prompted to select a cell on another worksheet which I do

The results output correctly as the name of the worksheet and the address of the cell with data
But then the name of the worksheet is output again and I receive an object defined error here:

Please Login or Register  to view this content.

Any thoughts on why this apparent "Loop Back"

thx
w

Please Login or Register  to view this content.

Please Login or Register  to view this content.

With the recursive call, a new instance of the procedure starts, and the current instance suspends (but doesn't end). So when the 2nd instance is complete, the original instance continues and then errors.

Put an Exit Sub after the Recursive call.

Alternatively, put a code Label at the top of the procedure and use a GoTo statement.

Please Login or Register  to view this content.

On another note: Don't you find using separate procedure calls just to display messages\prompts more complicated than having the code within the calling procedure?

Thanks AlphaFrog,

Don't you find using separate procedure calls just to display messages\prompts more complicated than having the code within the calling procedure?

Definitely.

For me, it's a discipline. I need to practice to force myself to break down as much as I can into Functions() and Subs().
I'll look at the "Exit Sub" later tonight.

thx
w

Thanks AlphaFrog,

The Exit Sub did the job perfectly!

thx
w