how to skip to next instance?
 

I have a 'admin' sheet where the workbook 'owner' can select which
external files to update -- this is done by setting the value in a
specific cell to 'yes' (via Validation). I've built the code to
update the specific file, but need to add code that will test to see
if that file is even available! Here's the code so far:
************
For Each r In Union(Worksheets("ADMIN").Range("c16:c34"),
Worksheets("ADMIN").Range("I16:I33"))
If LCase(r) = "yes" Then
Sto = r.Offset(0, -1).Value
Sto = Format(Sto, "000")

If Dir(Path & "Ops Update_Store" & Sto & ".xls") < "" Then

Else
Call MsgBox("Store " & Sto & " did not submit an update!",
vbExclamation, "Request Denied ....")
End If

Set mybook = Workbooks.Open(Path & "Ops Update_Store" &
Sto & ".xls", 0, True)

...... etc, etc, etc ....
************

You'll notice that I made an attempt to figure it out .... and the
msgbox appears when the file isn't available, BUT then continues on to
try to open the non-existent file, causing an error. I want the code
to display the msgbox and then continue to the next 'r' (if there is
one).

Any ideas?

thanks, ray

how to skip to next instance?
 

Move the open to the positive path in the if statement

For Each r In Union(Worksheets("ADMIN").Range("c16:c34"),
Worksheets("ADMIN").Range("I16:I33"))
If LCase(r) = "yes" Then
Sto = r.Offset(0, -1).Value
Sto = Format(Sto, "000")

If Dir(Path & "Ops Update_Store" & Sto & ".xls") < "" Then
Set mybook = Workbooks.Open(Path & "Ops Update_Store" & _
Sto & ".xls", 0, True)
Else
Call MsgBox("Store " & Sto & " did not submit an update!", _
vbExclamation, "Request Denied ....")
End If



"Ray" wrote:

I have a 'admin' sheet where the workbook 'owner' can select which
external files to update -- this is done by setting the value in a
specific cell to 'yes' (via Validation). I've built the code to
update the specific file, but need to add code that will test to see
if that file is even available! Here's the code so far:
************
For Each r In Union(Worksheets("ADMIN").Range("c16:c34"),
Worksheets("ADMIN").Range("I16:I33"))
If LCase(r) = "yes" Then
Sto = r.Offset(0, -1).Value
Sto = Format(Sto, "000")

If Dir(Path & "Ops Update_Store" & Sto & ".xls") < "" Then

Else
Call MsgBox("Store " & Sto & " did not submit an update!",
vbExclamation, "Request Denied ....")
End If

Set mybook = Workbooks.Open(Path & "Ops Update_Store" &
Sto & ".xls", 0, True)

...... etc, etc, etc ....
************

You'll notice that I made an attempt to figure it out .... and the
msgbox appears when the file isn't available, BUT then continues on to
try to open the non-existent file, causing an error. I want the code
to display the msgbox and then continue to the next 'r' (if there is
one).

Any ideas?

thanks, ray

how to skip to next instance?
 

Thanks Joel .... after your suggestion and moving my Else...End If
code to the right place, it works as desired!