Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
Hi Ken
and you have added the line on error goto errhandler -- Regards Frank Kabel Frankfurt, Germany Ken wrote: 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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 Ken wrote: 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. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Help
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 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - error saving file & error loading dll | Excel Discussion (Misc queries) | |||
Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |