On Error Help

K

Ken

We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re-set
the manual recalculation. What code would I use to do this?

TIA.

Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"

Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

CountRows = Range("A65536").End(xlUp).Row

For I = 1 To CountRows
If Left(ActiveCell, 2) = "AR" Or Left(ActiveCell,
2) = "RC" Or Left(ActiveCell, 2) = "RT" Then
ActiveCell.Offset(1, 0).Select
Else
If Left(ActiveCell, 3) <> "STN" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
Else
If BFYS <> ActiveCell.Offset(0,
3).Value Or Fund <> ActiveCell.Offset(0, 4).Value Then
BFYS = ActiveCell.Offset(0,
3).Value
Fund = ActiveCell.Offset(0,
4).Value
Selection.Clear
ActiveCell = BFYS
ActiveCell.Font.Bold = True
ActiveCell.Offset(0, 1) = Fund
ActiveCell.Offset(0, 1).Font.Bold
= True
ActiveCell.Offset(1, 0).Select

Else
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
End If
End If
End If
Next I

Range("A1").Select

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
F

Frank Kabel

Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
G

gigglefritz

A little tighter...

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
On Error GoTo errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]
End_it:
'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub

errhandler:
MsgBox "Error occured"
Resume End_it
End Sub

-----Original Message-----
Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re- set
the manual recalculation. What code would I use to do this?

TIA.
Click to expand...

.
Click to expand...
 
K

Ken

If it can not find the "F851.txt" file to open, it does
not go into the error trapping. I get:

Run-time error '1004':
'F851.txt' could not be found.....
-----Original Message-----
Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re-set
the manual recalculation. What code would I use to do this?

TIA.
Click to expand...

.
Click to expand...
 
F

Frank Kabel

Hi Ken
and you have added the line
on error goto errhandler


--
Regards
Frank Kabel
Frankfurt, Germany

If it can not find the "F851.txt" file to open, it does
not go into the error trapping. I get:

Run-time error '1004':
'F851.txt' could not be found.....
-----Original Message-----
Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re-set
the manual recalculation. What code would I use to do this?

TIA.
Click to expand...

.
Click to expand...
Click to expand...
 
K

Ken

Frank, yes, I have that line. Can it have somethnig to do
with the type of error? When the code gets to this open
error, I get the standard error message box as I described
below. It does not branch to errhandler:.
-----Original Message-----
Hi Ken
and you have added the line
on error goto errhandler


--
Regards
Frank Kabel
Frankfurt, Germany

If it can not find the "F851.txt" file to open, it does
not go into the error trapping. I get:

Run-time error '1004':
'F851.txt' could not be found.....
-----Original Message-----
Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


Ken wrote:
We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re- set
the manual recalculation. What code would I use to do this?

TIA.


.
Click to expand...
Click to expand...
.
Click to expand...
 
K

Ken

Thanks. I saw that someplace before and will include it in
my code.

I still have that errror branching problem though. See my
reply to Frank.
-----Original Message-----
A little tighter...

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
On Error GoTo errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]
End_it:
'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub

errhandler:
MsgBox "Error occured"
Resume End_it
End Sub

-----Original Message-----
Hi
try
Sub Test()

Dim CountRows As Double
Dim I As Double
Dim Fund As String
Dim BFYS As String

'Turn off warnings, auto recalc, etc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

ChDir "C:\Downloads"
on error goto errhandler
Workbooks.OpenText Filename:="C:\Downloads\F851.txt",
Origin:=xlWindows, _
StartRow:=4, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2), Array(15 _
, 2), Array(25, 2), Array(37, 2), Array(53, 3),
Array(65, 1), Array(71, 1), Array(91, 1), _
Array(111, 1))

'[....]

'Turn on warnings, auto recalc, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
exit sub

errhandler:
msgbox "Error occured"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

We have a macro which we begin by turning off manual
recalculation, screen updating, etc. Then we import a text
file and run some code. At the end we turn the manual
recalculation, screen updating, etc. back on.

If the text file is not found or if the macro somehow
bombs, I want to have an error trapping routine that will
give the user an error message and that will also re- set
the manual recalculation. What code would I use to do this?

TIA.
Click to expand...

.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can Vba workout if a selection is part of a group? 0
Array code troubles 4
Runtime error 91: Object variable or With block variable not set 8
Loop Repeats - PLS HELP 2
Run Time Error "13": Type mismatch 6
My macro doesn't work if sheet is protected but it works if not protected 2
Entering data from a userform into multiple sheets 2
Select Case 13

Top