Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error problem
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 & ":DZ" & cell.Row).ClearContents found_It: Next cell '==================================== any one got any ideas? Regards, Jason Quirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error problem
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) "WhytheQ" wrote in message ... 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 & ":DZ" & cell.Row).ClearContents found_It: Next cell '==================================== any one got any ideas? Regards, Jason Quirk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error problem
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" wrote in message ... 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 & ":DZ" & cell.Row).ClearContents found_It: Next cell '==================================== any one got any ideas? Regards, Jason Quirk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error problem
On 20 Dec, 19:53, "Chip Pearson" wrote:
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 Consultingwww.cpearson.com (email on the web site) "WhytheQ" wrote in message ... 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 & ":DZ" & cell.Row).ClearContents found_It: *Next cell '==================================== any one got any ideas? Regards, JasonQuirk- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Error problem | Excel Programming | |||
Problem with #VALUE/0! error | Excel Worksheet Functions | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |