Error 2023: How to identify cell in For Each Loop?

Please Login or Register  to view this content.

The program works fine on most files, so there is no code issue.

How to add some code to display which cell causes the program. Such as sheet name, row number, column number. This part of code will not be part of program, but it is used for programmer to find out which cell causes the issue. Probably some cells come with formulas and it does not work well with Trim function. Now I would like to add some code to find out which cell causes the first Error 2023.

Maybe I should use If Error Then Display message for user.

Thanks.

By the way, should I put On Error right before OneCell = WorksheetFunction.Trim(OneCell)? INSIDE IF statement? I guess it does not make sense to put it Outside For loop, since there are other codes after this For loop.

What does vbExclamation, "Trim Error" mean?

Does OneCell.Address cell its sheetname? It seems the answer is NO to me.

Thanks.

Originally Posted by AlphaFrog

Put On Error before the loop. No need to repeatedly turn it on within the loop. It's like a light switch, once it's on, it's on. You can turn it off after the loop ends with On Error GoTo 0

Select the MsgBox keyword in the code and press F1 on your keyboard for help on that function.

OneCell.Address(External:=True) will give the sheet name.

I like the idea of On Error GoTo 0, BUT what if there are other codes ? Does it bother other error handler? Take below code for example.

Regarding sheet name, I used Ws.Name and it works.

By the way, I think you should not put Exit Sub before TrimError:

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

If you don't, you'll get the msgboxes displayed even if you don't have an error.

How come? I have Exit Sub right before TrimError :

I have Exit Sub right before each error handler.

Originally Posted by AlphaFrog

Then why did you say this? That's what I commented on.

Actually, I should say: You should not put Exit Sub right after Next OneCell, since there are some other codes. It is just miscommunication.