Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
Hi I am trying to use the Find method in Excel (2003) I have appended a code snippet below, which works fine if the value can be found in a cell. If that value is not found, Excel returns a runtime error. Any ideas why this might be so? Regards Habib ---------------------- Range("E" & F & ":E" & L).Select FrstRow = Selection.Find(What:="CIB", LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
..find returns a range.
So FrstRow should be dimmed as a Range. Dim FrstRow as Range And with ranges, you'll have to use the Set statement. Set FrstRow = selection.find(...) Later you can tell if you found it or not: if frstrow is nothing then 'not found else 'it was found end if "HSalim[MVP]" wrote: Hi I am trying to use the Find method in Excel (2003) I have appended a code snippet below, which works fine if the value can be found in a cell. If that value is not found, Excel returns a runtime error. Any ideas why this might be so? Regards Habib ---------------------- Range("E" & F & ":E" & L).Select FrstRow = Selection.Find(What:="CIB", LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
You can use a range object to test if the value has been found e.g:
Dim fndCell As Range Dim FrstRow As Long With Range("E" & f & ":E" & l) Set fndCell = .Find(What:="CIB", LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False) End With If Not fndCell Is Nothing Then FrstRow = fndCell.Row 'continue Else MsgBox "Not found" End If Hope this helps Rowan HSalim[MVP] wrote: Hi I am trying to use the Find method in Excel (2003) I have appended a code snippet below, which works fine if the value can be found in a cell. If that value is not found, Excel returns a runtime error. Any ideas why this might be so? Regards Habib ---------------------- Range("E" & F & ":E" & L).Select FrstRow = Selection.Find(What:="CIB", LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
Hello Dave and Rowan,
Thanks for the quick response. I did see the set syntax in the help files but I was not getting anywhere with that either till i explicitly declared a range. I've been experiencing intermittent difficulty with intellisense not working - any idea why that might be happening. I am able to compile the priject so i do not have any missing references. Thanks again for the assistance. Regards Habib |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
Hi Habib
It is generally a good idea to use Option Explicit at the top of every module (if you set ToolsOptionsEditorRequire Variable Declaration this will be done automatically for you). You will then be forced to declare all variables and you should declare them explicitly wherever possible. This may also explain your intellisense issues. If you don't declare the range or declare it as a variant then you will not have access to intellisense for that variable/object. As soon as you declare it as a range you should have intellisense. Hope this helps Rowan HSalim[MVP] wrote: Hello Dave and Rowan, Thanks for the quick response. I did see the set syntax in the help files but I was not getting anywhere with that either till i explicitly declared a range. I've been experiencing intermittent difficulty with intellisense not working - any idea why that might be happening. I am able to compile the priject so i do not have any missing references. Thanks again for the assistance. Regards Habib |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 91
Rowan,
Thanks again!. Regards Habib "Rowan Drummond" wrote in message ... : Hi Habib : : It is generally a good idea to use Option Explicit at the top of every : module (if you set ToolsOptionsEditorRequire Variable Declaration : this will be done automatically for you). You will then be forced to : declare all variables and you should declare them explicitly wherever : possible. : : This may also explain your intellisense issues. If you don't declare the : range or declare it as a variant then you will not have access to : intellisense for that variable/object. As soon as you declare it as a : range you should have intellisense. : : Hope this helps : Rowan : : HSalim[MVP] wrote: : Hello Dave and Rowan, : Thanks for the quick response. : I did see the set syntax in the help files but I was not getting anywhere : with that either till i explicitly declared a range. : : I've been experiencing intermittent difficulty with intellisense not : working - any idea why that might be happening. I am able to compile the : priject so i do not have any missing references. : : Thanks again for the assistance. : Regards : Habib : : |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Interior class error/runtime error 1004 | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |