Error

R

Rafael Alvares Tubero

New Member
Joined
Mar 18, 2011
Messages
45
Hello everybody,

Guys, I was trying to run this code with a error treatment, but what happen it is failing when it goes through the second error treatment. Let me show you, the line where it's failing is in red:

Sub I8()
Dim i As Long
Dim n As String
Dim n1 As String
Dim MyName As String
Dim MyFile As Variant
Dim MyFile1 As Variant
'Dim dir As String
'Dim arq As String
'Dim arq1 As String
Dim ult_col As Long
Dim ult_lin As Long
Dim absoluto As Long

MyName = ThisWorkbook.Name
dir = Range("dir")
arq = Range("arq")
arq1 = Range("arq1")
n = Sheets("Feriados").Range("I8")
n1 = Sheets("Feriados").Range("I9")

Workbooks(MyName).Activate
Sheets("OnShoreA").Select
Columns("A:AN").ClearContents
Workbooks(MyName).Activate
Sheets("OnShoreB").Select
Columns("A:AP").ClearContents
Sheets("F5").Select
Range("A6:P65000").ClearContents
On Error GoTo filenotthere
Workbooks.OpenText Filename:=dir _
& "\" & arq _
, Origin:=xlWindows, StartRow:=1, 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, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1)), TrailingMinusNumbers:=True

arq = Left(arq, 11)
ActiveWorkbook.SaveAs Filename:= _
dir & "\" & arq & ".xls" _
, FileFormat:=xlNormal, CreateBackup:=False


GoTo Continue
Exit Sub
filenotthere:
MyPrompt = "Houve um erro ao abrir o arquivo. É possível que o "
MyPrompt = MyPrompt & "arquivo NÃO tenha sido transferido para esta pasta. Clique em Ok para "
MyPrompt = MyPrompt & "procurar o arquivo. "
'ou clique em Cancelar para NÃO fazer"
'MyPrompt = MyPrompt & "a Base do M6."
Ans = MsgBox(MyPrompt, vbOKCancel)

If Ans = vbCancel Then Exit Sub

MyFile = Application.GetOpenFilename
If MyFile = False Then Exit Sub

On Error GoTo 0

Workbooks.OpenText Filename:=MyFile, _
Origin:=xlWindows, StartRow:=1, 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, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1)), TrailingMinusNumbers:=True


arq = Left(arq, 11)

ActiveWorkbook.SaveAs Filename:= _
dir & "\" & arq & ".xls" _
, FileFormat:=xlNormal, CreateBackup:=False

Continue:

Workbooks(arq & ".xls").Activate
Sheets(1).Select
ult_col = Cells(1, Columns.Count).End(xlToLeft).Column
ult_lin = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(ult_lin, ult_col)).Copy
Workbooks(MyName).Activate
Sheets("OnShoreA").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(arq & ".xls").Close

On Error GoTo notfile
:crash:
Workbooks.OpenText Filename:=dir _
& "\" & arq1 _
, Origin:=xlWindows, StartRow:=1, 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, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1)), TrailingMinusNumbers:=True

arq1 = Left(arq1, 14)
ActiveWorkbook.SaveAs Filename:= _
dir & "\" & arq1 & ".xls" _
, FileFormat:=xlNormal, CreateBackup:=False


GoTo Continue1
Exit Sub
notfile:
MyPrompt = "Houve um erro ao abrir o arquivo. É possível que o "
MyPrompt = MyPrompt & "arquivo NÃO tenha sido transferido para esta pasta. Clique em Ok para "
MyPrompt = MyPrompt & "procurar o arquivo. "
'ou clique em Cancelar para NÃO fazer"
'MyPrompt = MyPrompt & "a Base do M6."
Ans = MsgBox(MyPrompt, vbOKCancel)

If Ans = vbCancel Then Exit Sub

MyFile = Application.GetOpenFilename
If MyFile = False Then Exit Sub

On Error GoTo 0

Workbooks.OpenText Filename:=MyFile, _
Origin:=xlWindows, StartRow:=1, 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, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1)), TrailingMinusNumbers:=True


arq1 = Left(arq1, 14)

ActiveWorkbook.SaveAs Filename:= _
dir & "\" & arq1 & ".xls" _
, FileFormat:=xlNormal, CreateBackup:=False


Continue1:

Workbooks(arq1 & ".xls").Activate
Sheets(1).Select
ult_col = Cells(1, Columns.Count).End(xlToLeft).Column
ult_lin = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(ult_lin, ult_col)).Copy
Workbooks(MyName).Activate
Sheets("OnShoreB").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(arq1 & ".xls").Close


may you guys help me?

thanks,

Rafael
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
I think you're code is jumping about all over the place with your various On Error Go To... It makes it very difficult to follow what you're doing let alone suggest why it is going wrong where it is

I think you'd be better off drawing a diagram of exactly what is happening and how you want to capture errors and evaluating if what you're doing is the most efficient.

Finally, this part of the code:
Workbooks(MyName).Activate
Sheets("OnShoreA").Select
Columns("A:AN").ClearContents
Workbooks(MyName).Activate
Sheets("OnShoreB").Select
Columns("A:AP").ClearContents
Sheets("F5").Select
Range("A6:P65000").ClearContents

Could be rewritten as:
Code: 
Workbooks(MyName).Activate
Sheets("OnShoreA").Columns("A:AN").ClearContents
Sheets("OnShoreB").Columns("A:AP").ClearContents
Sheets("F5").Range("A6:P65000").ClearContents
 
Upvote 0
Once you use On Error Goto, everything after the label you jump to becomes an error handler routine and any error in an error handler causes a run time error. You have to have a Resume statement in order to resume normal error handling. This is why you should only have one error handler block (at the end of the routine).
It owuld be far better for you to work out why the errors are occurring and avoid them occurring. For example, if you try to open a file that does not exist, you should use Dir(filepath) to test if the file exists before you attempt to open it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Delimiters in VBA has different result than manual
Replies
7
Views
139
Peter_SSs
Peter_SSs
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
338
MARK858
MARK858
J
  • Question Question
Text to Columns in Macro
2
Replies
10
Views
734
Alex Blakenburg
A
M
  • Solved
open text file
Replies
5
Views
326
Joe4
Joe4
E
  • Solved
Force Excel 2019 VBA to import multiple txt file to existing sheet & not seperate wk.book / wk.sheets
Replies
1
Views
654
Domenic
D

Forum statistics

Threads
1,224,606
Messages
6,179,865
Members
452,948
Latest member
UsmanAli786

Share this page

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