Do Loop

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
I am writing Do loop procedut=ere that works well. It goes to open a file, if the file name is missing the error handler sends the code to look for the next file name in a list and starts to Do Loop again.

This works until there are two files missing in a row, then the error handler stops working. Why is that , and what can I do?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hello, Keith,
posting your code would make it easier to explain
you've probably some code like "on error goto ..."
once you got an error, it has to be handled before a second one occurs
perhaps an on error resume next would do, but it's better to handle errors yourself, instead of letting the code jump to the next instruction
two procedures to shade some light
Code:
Sub test()
shn = "kilimanjaro"
On Error Resume Next
Sheets(shn).Select
MsgBox "did you see the sheet " & shn & "?", 32, UCase(shn)
On Error GoTo skip
Sheets(shn).Select
MsgBox "not displayed"
skip:
MsgBox "did you see the sheet " & shn & "?", 32, "SKIP"
Sheets(shn).Select 'error occuring
End Sub

Public Sub ErrorDemo()
On Error GoTo TheErrHandler

    ' FIRST ERROR
8     MakeError = 5 / 0
    MsgBox "I'm back  1"
    
    ' SECOND ERROR
22     MakeError2 = 5 / 0
    MsgBox "I'm back  2 "
    
    Exit Sub

TheErrHandler:

    ' HOW TO HANDLE FIRST ERROR
    If Erl = 8 Then Resume Next
    
    ' HOW TO HANDLE SECOND ERROR
    If Erl = 22 Then Exit Sub

End Sub
in your case it should be good to check if the files exists before trying to open them
do a search for FileExists
see this code
run "test"
Code:
Function Chk(myFile As String) As Boolean
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Chk = fso.FileExists(myFile)
Set fso = Nothing
End Function

Sub testIt()
If Chk("c:\temp\test.xls") Then MsgBox "Yes indeed."
End Sub
does this help ?
kind regards,
Erik
 
Upvote 0
Hi Eric,

This is the gist of it. I'm sure there is a better way to write it, I just havn't found it yet.





Sub Filename()

Sheets("Sheet2").Visible = True
Sheets("Station").Visible = True


On Error GoTo Label1
Sheets("Sheet2").Select
Sheets("Sheet2").Range("C34").Value = Sheets("Sheet2").Range("D2").Value
StrName = "IOPs" & " " & Sheets("Sheet2").Range("C34") & " " & Sheets("Sheet2").Range("A18")
Workbooks.Open Filename:= _
"C:\Documents and Settings\All Users\Documents\" & StrName
ActiveSheet.Range("A1:D22").Select
Selection.Copy
Application.ActiveWindow.ActivatePrevious
Sheets("Station").Select
Sheets("Station").Range("A1").Select
ActiveSheet.Paste
Application.ActiveWindow.ActivateNext
Application.CutCopyMode = False
ActiveWindow.Close
ShName = "IOP" & " " & Sheets("Sheet2").Range("A18")
Sheets(ShName).Range("A30:C52").Value = Sheets("Station").Range("E2:G24").Value


Label1:
On Error GoTo Label2
Sheets("Sheet2").Select
Sheets("Sheet2").Range("C34").Value = Sheets("Sheet2").Range("D3").Value
StrName = "IOPs" & " " & Sheets("Sheet2").Range("C34") & " " & Sheets("Sheet2").Range("A18")
Workbooks.Open Filename:= _
"C:\Documents and Settings\All Users\Documents\" & StrName
ActiveSheet.Range("A1:D22").Select
Selection.Copy
Application.ActiveWindow.ActivatePrevious
Sheets("Station").Select
Sheets("Station").Range("A1").Select
ActiveSheet.Paste
Application.ActiveWindow.ActivateNext
Application.CutCopyMode = False
ActiveWindow.Close
ShName = "IOP" & " " & Sheets("Sheet2").Range("A18")
Sheets(ShName).Range("A54:C76").Value = Sheets("Station").Range("E2:G24").Value

Label2:
On Error GoTo Label3
Sheets("Sheet2").Select
Sheets("Sheet2").Range("C34").Value = Sheets("Sheet2").Range("D4").Value
StrName = "IOPs" & " " & Sheets("Sheet2").Range("C34") & " " & Sheets("Sheet2").Range("A18")
Workbooks.Open Filename:= _
"C:\Documents and Settings\All Users\Documents\" & StrName
ActiveSheet.Range("A1:D22").Select
Selection.Copy
Application.ActiveWindow.ActivatePrevious
Sheets("Station").Select
Sheets("Station").Range("A1").Select
ActiveSheet.Paste
Application.ActiveWindow.ActivateNext
Application.CutCopyMode = False
ActiveWindow.Close
ShName = "IOP" & " " & Sheets("Sheet2").Range("A18")
Sheets(ShName).Range("A78:C100").Value = Sheets("Station").Range("E2:G24").Value
Label3:

Sheets("UpLoad").Select
End Sub
 
Upvote 0
Keith,

also firefytr is here :)
(in fact he asked you for the code)

you didn't post feedback on the examples you received ...
step through them with function key F8 to see what happens

you are trying to open files
see the code you received to first check if the files exists
if it doesn't then don't do the next lines, but jump to next file

Code:
If Chk("c:\temp\test.xls") Then
'your code
End If

if you don't get out, it would be good when you exaplined what you're trying to do
to loop through ranges
Code:
For Each c in Range("D1:D5")
MsgBox C.Address
'or try this Sheets("Sheet2").Range("C34").Value = Sheets("Sheet2").c.Value 
Next c
again use F8 to step through

best regards,
Erik (sleeping)
 
Upvote 0
Okay, so can you explain the logic you have here?? You're wanting to loop through files and such, but not much else has been laid out. Erik has done a fine job of shooting in the dark, but I'm not sure what other help we can give you if you do not be more specific.
 
Upvote 0
Thanks a lot , you guys for your help.
I thought the code was getting too complex for the thing I was trying to do , so I went another way. But you gave me some good ideas.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top