Error Handling in loop - go to next x

[Phiabell]

Hi,

I am trying to handle errors in my macro. The code goes down a number of rows, opens a worksheet based on that row, and copys a value from that worksheet into a cell in that row, then moves down.

On a error I'd like to

a) have the word 'error' in the cell where the value would be and
b) skip to the next row

this is the code I have so far - but this does not skip to the next x, but resumes in the middle of the code which has the effect of trying to shut down my main workbook (as the code says close workbook, at which point the secondary sheet would be open, but isn't, as the error is generally in finding the file to open.)

How can I get it to skip to next x?

Please Login or Register  to view this content.

Thanks for help

[Norie]

What's the cause of the error?

Which line of code does it happen on?

Is the problem that some of the workbooks you are trying to open don't exist?

[Andrew-R]

The straight path through the mud would be:

Please Login or Register  to view this content.

But it would be far better to test for the existence of each file before you tried to open it:

Please Login or Register  to view this content.

[Phiabell]

Andrew, thank you

That's what I was looking for - I had the components before I think but they weren't in the right order.
I think this is the solution I want as often the error will be the file path having changed name slightly - so the value will exist but the file won't. The 'error' return will prompt user to update the value.

Only problem is, having tested this new code, is that although x = 6 to 37, it keeps going and entering 'error' thousands of lines down until excel crashed :-/

[Andrew-R]

You need to move your Exit Sub Line from where it is to the line immediately above where the error handler starts...

Please Login or Register  to view this content.

[Norie]

This is what I as thinking of.

Please Login or Register  to view this content.

[Phiabell]

Thank you, works perfectly.

Norie thanks for the alternative option, though I think I will go with the other due to the value sometimes being present but incorrect.

[Norie]

The code I posted doesn't check to see if a value is there.

It's checking for the file and path the value is supposed to be referring to.

If they aren't found 'Error' is put in column D.

[Phiabell]

Ah ok, thanks, I will try that too and see if one suits the process better.