On Error problem

[WhytheQ]

not too sure why the attached doesn't work.
It seems like the error generated by the vlookup isn't covered by "On
Error" - the error is 1004

'====================================
For Each cell In .Range("T9:T369").Cells
On Error GoTo found_It:
myRoom = WorksheetFunction.VLookup(cell.Value,
ThisWorkbook.Range("myRange"), 1, False)
.Range("T" & cell.Row & "Z" & cell.Row).ClearContents
found_It:
Next cell
'====================================

any one got any ideas?

Regards,
Jason Quirk

 

[Bob Phillips]

It doesn't if the cell is empty, so test that first.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

The "GoTo" in an On Error Goto <Label> statement doesn't work quite the same
way as a normal Goto does. VBA executes in one of two "modes": normal mode
and error mode. When an error is encountered and is handled by a Goto
<Label> statement, execution is in error mode and will remain in this mode
until the mode is switched back to normal mode with an "Exit
Sub/Function/Property" statement or a Resume statement. If a run time error
is encountered when code is executing in error mode, no error trapping is
done and the error is not trapped by the On Error statement. It breaks on
the error regardless of any On Error statement.

Your code needs to execute a Resume statement to get out of error mode and
back into normal mode. You could use code similar to the following example:

Dim R As Range
On Error GoTo FoundIt
For Each R In Range("A1:A5")
Debug.Print R.Address, R.Value
' deliberately raise an error if R = 123
If R.Value = 123 Then
Err.Raise vbObjectError + 1
End If
FoundIt:
If Err.Number <> 0 Then
' clear the error and use Resume to
' get back in normal, not error, mode.
Err.Clear
Resume FoundIt
End If
Next R



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[WhytheQ]

The "GoTo" in an On Error Goto <Label> statement doesn't work quite the same
way as a normal Goto does. VBA executes in one of two "modes": normal mode
and error mode. When an error is encountered and is handled by a Goto
<Label> statement, execution is in error mode and will remain in this mode
until the mode is switched back to normal mode with an "Exit
Sub/Function/Property" statement or a Resume statement. If a run time error
is encountered when code is executing in error mode, no error trapping is
done and the error is not trapped by the On Error statement.   It breakson
the error regardless of any On Error statement.

Your code needs to execute a Resume statement to get out of error mode and
back into normal mode. You could use code similar to the following example:

Dim R As Range
On Error GoTo FoundIt
For Each R In Range("A1:A5")
    Debug.Print R.Address, R.Value
    ' deliberately raise an error if R = 123
    If R.Value = 123 Then
        Err.Raise vbObjectError + 1
    End If
FoundIt:
    If Err.Number <> 0 Then
        ' clear the error and use Resume to
        ' get back in normal, not error, mode.
        Err.Clear
        Resume FoundIt
    End If
Next R

--
Cordially,
Chip Pearson
Microsoft MVP  - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)









- Show quoted text -

THANKS FOR ALL THE HELP.
Works fine now + I now understand OnError better

Happy Christmas (if you're into that sort of thing)
Jason