trapping error

[RobcPettit]

Sub ToManyPrices()
I can not seem to get this Error trap to work, when I find the first file that
is not in the directory my msgbox works, when I find a 2nd file that is not in
the dir the program crashes, could you advise why.

Application.Goto Reference:="Dates2"
Collectdat = Range("Dates2").Value
If Range("f8") <> "" Then
BeYondDate = Range("f7", Range("f7").End(xlDown)) ' this gets the list of files
to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If

For i =1 To UBound(BeYondDate)

On Error GoTo Nofile
Workbooks.OpenText Filename:="I:\My Documents\sharescope export\" &
BeYondDate(i, 1) & ".prn", _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
_
Array(2, 1), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 1),
Array(7, 9)), _
TrailingMinusNumbers:=True

Name = Range("a1") 'epic

Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates

Workbooks(BeYondDate(i, 1) & ".prn").Close False 'closes share file
comparedat


Nofile:
MsgBox ("File " & "'" & BeYondDate(i, 1) & "'" & " not found in Sharescope
Export")


Next i

End Sub

 

[Guest]

I didn't go through all your code but the MsgBox located after your NoFile label should NOT have the parentheses around the arguments. This is only necessary when setting the response of MsgBox to some value. You get an error normally when you try using MsgBox this way. Could that be why your error trapping isn't working

HTH

 

[Dave Peterson]

One way around it:

Sub ToManyPrices()
Application.Goto Reference:="Dates2"
Collectdat = Range("Dates2").Value
If Range("f8") <> "" Then
BeYondDate = Range("f7", Range("f7").End(xlDown))
' this gets the list of files to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If

On Error GoTo Nofile
For i = 1 To UBound(BeYondDate)
Workbooks.OpenText Filename:="I:\My Documents\sharescope export\" & _
BeYondDate(i, 1) & ".prn", _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9), _
Array(4, 9), Array(5, 9), Array(6, 1), _
Array(7, 9)), _
TrailingMinusNumbers:=True

Name = Range("a1") 'epic

Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates

Workbooks(BeYondDate(i, 1) & ".prn").Close False

getNextFile:

Next i
On Error Resume Next
'more code???

Exit Sub

Nofile:
MsgBox ("File " & "'" & BeYondDate(i, 1) & "'" & " not found in Sharescope
Export ")

Resume getNextFile:

End Sub

===
but you could get rid of the error checking and just check the for the existence
of each of the files:

Sub ToManyPrices()

Dim myFileName As String

'Application.Goto Reference:="Dates2"
'Collectdat = Range("Dates2").Value
If Range("f8") <> "" Then
beYonddate = Range("f7", Range("f7").End(xlDown))
' this gets the list of files to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If


For i = 1 To UBound(beYonddate)
myFileName = "I:\My Documents\sharescope export\" & _
beYonddate(i, 1) & ".prn"
If Dir(myFileName) = "" Then
MsgBox "File " & "'" & beYonddate(i, 1) & "'" & _
" not found in Sharescope Export "
Else
Workbooks.OpenText Filename:=myFileName, _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9), _
Array(4, 9), Array(5, 9), Array(6, 1), _
Array(7, 9)), _
TrailingMinusNumbers:=True

Name = Range("a1") 'epic

Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates

Workbooks(beYonddate(i, 1) & ".prn").Close False
End If
Next i

End Sub

And this line in either procedure looks funny to me:
Name = Range("a1") 'epic

Does name belong to something: activesheet.name????
or is it a variable.

If it's a variable, I'd change it to something that isn't builtin (myName???).