Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

On Error GoTo... Runs OK in the first time. But fails next time

  Asked By: Kerri    Date: Aug 13    Category: MS Office    Views: 998
  

I am facing this problem on the On Error GoTo statement. It is
located inside a loop. During the first run it when the error occurs
it performs OK and goes to the GoTo label. But during the second
time when it encounters the error the code fails, that means it
doesn't go to the GoTo label and the run time error pops up. I am
attaching the code below. As you can see I have tried to put the On
Error and GoTo statement at many places. But every time I have had
the run time error pop up during the second run.
As you can see the code is not very well structured. I am begginer
to VBA and try to learn it improve my efficiency in Excel. Your
inputs on more structured code would also help.
Sub populateEmpElasticityTable()
Dim colAdjR2, colObs, colSlope, colIntcpt, colTstat
colAdjR2 = 2
colObs = 2
colSlope = 2
colIntcpt = 2
colTstat = 4
Dim rowAdjR2, rowObs, rowSlope, rowIntcpt, rowTstat
rowAdjR2 = 6
rowObs = 8
rowSlope = 18
rowIntcpt = 17
rowTstat = 18

'create the sheet name
'read the sheet for the specified cells
'and write them to the specified sheets
Dim wbTo, wbFrom1, wbFrom2 As Workbook
Set wbTo = Workbooks("EmpElasticityTable.xls")
Set wbFrom2 = Workbooks("elasticityBook2Regression2.xls")
Set wbFrom1 = Workbooks("elasticityBook2Regression.xls")
Dim shtTo As Worksheet
Set shtTo = wbTo.Worksheets("Consolidate")
Dim rowTo
rowTo = 3
Dim colTo
colTo = 5
Dim shtName As String
Dim shtName1 As String
Dim shtName2 As String
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
'MsgBox shtName
Dim shtFrom As Worksheet
'On Error GoTo BadSheetName
Do While shtTo.Cells(rowTo, 1) <> Empty
'On Error GoTo BadSheetName
Do While shtTo.Cells(1, colTo) <> Empty
MsgBox shtName
On Error GoTo BadSheetName
'On Error Resume Next
Set shtFrom = wbFrom1.Worksheets(shtName)
'On Error GoTo BadSheetName
MsgBox shtName
MsgBox shtFrom.Cells(rowAdjR2, colAdjR2).Value

shtTo.Cells(rowTo, colTo).Value = shtFrom.Cells(rowAdjR2,
colAdjR2).Value

shtTo.Cells(rowTo, colTo + 1).Value = shtFrom.Cells(rowObs,
colObs).Value

shtTo.Cells(rowTo, colTo + 2).Value = shtFrom.Cells(rowIntcpt,
colIntcpt).Value

shtTo.Cells(rowTo, colTo + 3).Value = shtFrom.Cells(rowSlope,
colSlope).Value

shtTo.Cells(rowTo, colTo + 4).Value = shtFrom.Cells(rowTstat,
colTstat).Value

'BadSheetName:
'On Error GoTo 0
'MsgBox "Out"
colTo = colTo + 5
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
Loop

colTo = 5
rowTo = rowTo + 1
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
BadSheetName:
On Error GoTo 0
MsgBox "Out"
colTo = colTo + 5
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
Loop

MsgBox "done"

End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Ernesto Robinson     Answered On: Aug 13

Looking at the online help  for "On Error", "On error  GoTo 0"
disables any enabled error handler in the current procedure, so you
need to get rid of that if you want to keep your error handler.

Then, to make sure your code  does not fall into your error handler
by mistake, you generally put  an "Exit Sub" before it.

Next, you need a Resume (rather than Loop) at the end of the error
handler.

 
Answer #2    Answered By: Marion Hayes     Answered On: Aug 13

The Online help  states correctly that "On error  GoTo 0" disables the
error handler in the current procedure.
However the book "Excel 2003 Power Programming with VBA" misguided
me. This what is written in the book. " On Error GoTo 0 - After
executing this statement, VBA resumes its normal error-checking
behavior. Use this statement  after using one of the other On Error
statements or when you want to remove error handling in your
procedure."

 




Tagged: