OnError not trapping subsequent errors?

[ker_01]

I am working with an org chart "tree" in table form to find the reporting
heirachy. I start with the employee name, look it up on another table, and
find that person's reporting manager. Then I look up that manager as an
employee to find their manager, and so on.

Eventually each tree hits an error when I get to the top of the tree,
because it can't find a matching manager name. So, I use onError to escape
the loop and present the data onscreen for review.

This works fine the first time, but the code stops with an error on the
second 'original' employee- it is like the code forgot about the OnError
statement. I even added a goto 0 statement in case it needs some type of
'reset', but that didn't help.

Any ideas?
Thanks,
Keith

sorry for the linewrap...

Sub CheckHRSTCNames()

Dim OutArr(1 To 10)
SHRRowCnt = lastRow(Sheets(7))
SourceHRIDArr = Sheets(7).Range("A1:A" & SHRRowCnt)
SourceHRMgrArr = Sheets(7).Range("B1:B" & SHRRowCnt)

For tempRow = 2 To lastRow(Sheets(6))

Erase OutArr

tID = Sheets(6).Range("A" & tempRow).Value
matchRow = Application.Match(CStr(tID), SourceHRIDArr, False)
'Debug.Print tID

On Error GoTo ShowResults
For p = 1 To 10
OutArr(p) = Sheets(7).Range("AD" & matchRow).Value '<--errors here
on second loop, when p gets to about 7 or 8 depending on the employee
matchRow = Application.Match(OutArr(p), SourceHRMgrArr, False)
Next

ShowResults:
MsgBox OutArr(1) & ", " & OutArr(2) & ", " & OutArr(3) & ", " &
OutArr(4) & ", " & OutArr(5) & ", " & _
OutArr(6) & ", " & OutArr(7) & ", " & OutArr(8) & ", " &
OutArr(9) & ", " & OutArr(10)
On Error GoTo 0

Next

End Sub

 

[Bob Phillips]

Haven't tested it as I cannot visualise the data, but see if this works

Sub CheckHRSTCNames()
Dim OutArr(1 To 10)
Dim tId As Variant
Dim MatchRow As Long
Dim LookUp As String

SHRRowCnt = LastRow(Sheets(7))
SourceHRIDArr = Sheets(7).Range("A1:A" & SHRRowCnt)
SourceHRMgrArr = Sheets(7).Range("B1:B" & SHRRowCnt)

For tempRow = 2 To LastRow(Sheets(6))

Erase OutArr

tId = Sheets(6).Range("A" & tempRow).Value
LookUp = CStr(tId)

For p = 1 To 10

On Error Resume Next
MatchRow = Application.Match(LookUp, SourceHRIDArr, False)
On Error GoTo 0
If MatchRow = 0 Then Exit For

OutArr(p) = Sheets(7).Range("AD" & MatchRow).Value
LookUp = OutArr(p)
Next

MsgBox OutArr(1) & ", " & OutArr(2) & ", " & OutArr(3) & ", " &
OutArr(4) & ", " & OutArr(5) & ", " & _
OutArr(6) & ", " & OutArr(7) & ", " & OutArr(8) & ", " &
OutArr(9) & ", " & OutArr(10)
Next
End Sub

 

[ker_01]

Bob- thank you, I used a variation of your logic;
If IsError(matchrow) Then GoTo ShowResults

I'm curious though- is there a reason why Excel would "lose" the ability to
forward subsequent errors through the error handling procedure? I'd like to
understand what happened so I can program around it if I have a similar
situation in the future. Is there an error buffer that I need to clear or
something?

Thanks!
Keith

 

[OssieMac]

Did your error send it to an error routine and in the error routine you used
GoTo a labelName instead of Resume labelName? This one think that will cause
the problem you describe. (That is it works for the first time the error
occurs then fails on any subsequent errors.)

 

[Bob Phillips]

When you set an error handler, VBA has problems if you disable it later and
then try to reset. It is better to Resume the code at some point when the
error has been handled, something like (untested)

Sub CheckHRSTCNames()
Dim OutArr(1 To 10)

SHRRowCnt = LastRow(Sheets(7))
SourceHRIDArr = Sheets(7).Range("A1:A" & SHRRowCnt)
SourceHRMgrArr = Sheets(7).Range("B1:B" & SHRRowCnt)

On Error GoTo ShowResults

For tempRow = 2 To LastRow(Sheets(6))

Erase OutArr

tId = Sheets(6).Range("A" & tempRow).Value
MatchRow = Application.Match(CStr(tId), SourceHRIDArr, False)

For p = 1 To 10
OutArr(p) = Sheets(7).Range("AD" & MatchRow).Value
MatchRow = Application.Match(OutArr(p), SourceHRMgrArr, False)
Next

ShowResults:
MsgBox OutArr(1) & ", " & OutArr(2) & ", " & OutArr(3) & ", " &
OutArr(4) & ", " & OutArr(5) & ", " & _
OutArr(6) & ", " & OutArr(7) & ", " & OutArr(8) & ", " &
OutArr(9) & ", " & OutArr(10)
Resume Next
Next
End Sub


But it is better the way I showed (and you adpated) IMO as it is not an
error, just a foreseeable situation, so handle it. Error handling should be
for real errors.