On Error Statements in VBA

Lino

Active Member
Joined
Feb 27, 2002
Messages
429
Hello,

I am using the following code:

'For Each Cell in Range(A1:A10)
Cell.value = ThisValue
On Error GoTo ReadNextCell
Workbook.open....ThisValue
On Error GoTo 0

ReadNextCell:
Next Cell

What is happening is that when ThisValue doesn't exist in the opening destination folder it should GoTo ReadNextCell and resume at Next Cell.

This script works the first time but the next time it happens I get a run time error telling me that the file I am trying to open does not exist.

Why does this only work the first time...it appears it is ignoring the On Error GoTo ReadNextCell statement...Why?

Thanks,

Lino
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Lino

Active Member
Joined
Feb 27, 2002
Messages
429
I tried the code on the attached link...however unless I use an exit sub command before the error handling code it still does not work propertly.

With the recommended code the error handling code runs even if there isn't an error which generates another runtime error "20"..."Resume Without Error"

Because it is a For Loop I do not want to use exit sub because then it will exit the loop and this is not my intention.

I am simply trying to tell the system...if the file does not exist skip it and go to the next cell...see what the value is...try to open the file...if it doesn't exist again move on to the next cell.

Thanks,

Lino
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Lino,

Have you tried On Error Resume Next ?

<pre>Sub test()
Dim ThisValue As String, Cell As Range

For Each Cell In Range("A1:A10")
ThisValue = Cell.Value
On Error Resume Next
Workbooks.Open ThisValue
On Error GoTo 0
Next Cell

End Sub</pre>
HTH

PS. I swapped the ThisValue to the start of the line as I didn't follow the logic the other way around. Let me know if I'm missing something.
 

Lino

Active Member
Joined
Feb 27, 2002
Messages
429

ADVERTISEMENT

On Error Resume Next only brings me to the next line of code...that is no good because if the "ThisValue" which is a filename can not be opened the rest of the code can't realistically run.

It needs to loop to the next value in the next cell
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Lino,

I think the code I gave you achieves what you are trying to do; if the workbook can't be opened the loop moves to the next workbook reference.

Anyway, looking back at your post further up you say that you can't get the error-handling routine to work unless you place an 'Exit Sub' before it. That's exactly what you are supposed to do :) - to stop it running as the last part of your main routine. (Have a look at the example under 'Error object' in the VBE Help). It should, however, be at the end of the sub and not within a loop. You just include a Resume Next statement in the error-handling to return execution to the loop.

HTH

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-11-05 10:17
 

Lino

Active Member
Joined
Feb 27, 2002
Messages
429

ADVERTISEMENT

I don't think your code achieves what i need it to do...after On Error GoTo 0 I have a lot more code to run through. The Next Cell doesn't appear until just before End Sub. So On Error Resume Next will jump to the next line which is On Error GoTo 0 then it will go the next line of code and so on until it gets to Next Cell.

What I said about the exit sub is that if I don't use it the code will run even if there isn't an error so if I use the Exit sub it will stop the procedure before it runs the error handling code...this is no good because then it exits the entire For Each Loop and I don't want this to happen what would be the point?
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Just to save any further confusion, for me or anybody else, how about posting the whole routine so we get the whole picture?
 

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
How about checking for the file's existence first?

Something like this:

Code:
    For Each Cell In Range("A1:A10")
        
        If Dir(ThisValue)<> "" Then
        
            Cell.Value = ThisValue
            Workbooks.Open ThisValue
            ' Other code here
        End If
    Next Cell

So you just put everything inside of an if statememt...

If you have a certain directory that you are searching in then you just do something like this:<pre><font color='#000000'>
Dir("C:\" & ThisValue)</font></pre>

or<pre><font color='#000000'>
Dir(ThisPath & ThisValue)</font></pre>

if you use the latter, make sure your path has a backslash at the end.

HTH,

Russell
This message was edited by Russell Hauf on 2002-11-05 11:28
 

IANW

New Member
Joined
Jun 18, 2002
Messages
5
if you want to keep it similar to your existing code you could just put the actions that you want to perform in an if statement with a simple error check

Sub errorcheck()
For Each cell In Range("a4:a10")

ThisValue = cell.Value
x = 0
On Error GoTo collect
Workbooks.Open (ThisValue)
On Error GoTo 0
If x = 1 Then
'insert code here for workbooks that do open
End If


Next cell
'insert more code here if there is any
'
Exit Sub
collect:
x = 1
Resume Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top