Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using FIND in Visual Basic
I am trying to use the Selection.Find in a macro, and want to know how to get
past the part when it cannot find a cell matching the criteria. It works fine when it can find something, but when it does not find it, I get a Run-time error 91 (Object variable or With Block variable not set). Does it have something to do with the Activate part of my code? I am using the following: Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate In other words, is there a way to ask it to find, but without activating? I want it to do something else if it cannot find a cell matching the criteria. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using FIND in Visual Basic
Use a range object something like this...
dim rngFound as range set rngfound = Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) if rngfound is nothing then msgbox "Not Found" else ... end if -- HTH... Jim Thomlinson "Mike" wrote: I am trying to use the Selection.Find in a macro, and want to know how to get past the part when it cannot find a cell matching the criteria. It works fine when it can find something, but when it does not find it, I get a Run-time error 91 (Object variable or With Block variable not set). Does it have something to do with the Activate part of my code? I am using the following: Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate In other words, is there a way to ask it to find, but without activating? I want it to do something else if it cannot find a cell matching the criteria. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using FIND in Visual Basic
thanks. I'll give that a shot.
"Jim Thomlinson" wrote: Use a range object something like this... dim rngFound as range set rngfound = Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) if rngfound is nothing then msgbox "Not Found" else ... end if -- HTH... Jim Thomlinson "Mike" wrote: I am trying to use the Selection.Find in a macro, and want to know how to get past the part when it cannot find a cell matching the criteria. It works fine when it can find something, but when it does not find it, I get a Run-time error 91 (Object variable or With Block variable not set). Does it have something to do with the Activate part of my code? I am using the following: Selection.Find(What:="20?-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate In other words, is there a way to ask it to find, but without activating? I want it to do something else if it cannot find a cell matching the criteria. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you use visual basic to find cross product of two vectors | Excel Worksheet Functions | |||
visual basic. find copy row past into new sheet | Excel Worksheet Functions | |||
Where do I find help for Visual Basic for Applications Extensibili | Excel Programming | |||
Using Find/Replace in Visual basic to adapt the code | Excel Programming | |||
Visual Basic code to find next available (blank) row in a spreadsh | Excel Programming |