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?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hello,

Can you post your code please?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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.
 

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Thanks for posting back Keith. We really appreciate that. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top