Error trap with object variable problem
Hello, I have a macro with a series of different error traps which are set
at appropriate points in a loop. The errors I want to trap are when the Find method doesn't find what it's looking for. The code I use looks like this: ' Find the Oracle RefNo & Assignment from the EmpNo sheet On Error GoTo ErrorTrap EmpNoRow = EmpNoSheet.Columns("C:C").Find(What:=InDataSheet.C ells(InDataRow, 3)).Row there is a value in the specified cell, and all of the object variables are appropriately set. The code works fine when the value is found, but when it is missing it displays the Debug dialog box (which is exacly what I want to trap) . It says: Run time Error 91 - Object variable or with block not set. Strangely though, sometimes my error traps do work with identical code. Any ideas? Thanks |
Error trap with object variable problem
Hello Mike H, :)
Please use a range type variable instead of EmpNoRow. It make trappling easy. When the value is found, a variable rngFound has the range, if th value is not found a variable rngFound has Nothing. Code ------------------- Dim rngFound As Range Set rngFound = EmpNoSheet.Columns("C:C").Find(What:=InDataSheet.C ells(InDataRow, 3)) If rngFound Is Nothing Then 'NOT FOUND GoTo ErrorTrap ' As you like Else 'FOUND EmpNoRow = rngFound.Row End If ------------------- -- Message posted from http://www.ExcelForum.com |
All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com