Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find woe
I've been trying to get the following code to work in Excel 2003. It's fine
if X is found. It breaks on the X assignment statement if Cells.Find fails to find anything. The error is *always*: Object variable or With block variable not set (Error 91) I've tried it with and without using the "set" in front of the X assignment, with and without declaring DIM X, alternately as Object and as Range Range("A1").Select x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate If (x Is Nothing) Then MsgBox "Not Found" Else MsgBox "x is " & x End If So, while it works okay with the "On Error Resume Next" statement inserted, I must be doing something wrong for it to generate a stop execution error...I'd like to find out what is the proper way of doing this. Can anyone give me a clue? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find woe
Hi Jess,
Try: '============= Public Sub Tester() Dim Rng As Range Const strSearch As String = "abc" Set Rng = Cells.Find(What:=strSearch, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not Rng Is Nothing Then MsgBox strSearch & " found at " & Rng.Address(0, 0) Else MsgBox strSearch & " not found" End If End Sub '<<============= --- Regards, Norman "Jess Wundring" wrote in message ... I've been trying to get the following code to work in Excel 2003. It's fine if X is found. It breaks on the X assignment statement if Cells.Find fails to find anything. The error is *always*: Object variable or With block variable not set (Error 91) I've tried it with and without using the "set" in front of the X assignment, with and without declaring DIM X, alternately as Object and as Range Range("A1").Select x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate If (x Is Nothing) Then MsgBox "Not Found" Else MsgBox "x is " & x End If So, while it works okay with the "On Error Resume Next" statement inserted, I must be doing something wrong for it to generate a stop execution error...I'd like to find out what is the proper way of doing this. Can anyone give me a clue? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find woe
One way:
Dim x As Range Set x = Cells.Find( _ What:=",", _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not x Is Nothing Then MsgBox "x is " & x.Value Else MsgBox "Not Found" End If The problem you're having is that you're trying to activate a non-existent range. In article , "Jess Wundring" wrote: I've been trying to get the following code to work in Excel 2003. It's fine if X is found. It breaks on the X assignment statement if Cells.Find fails to find anything. The error is *always*: Object variable or With block variable not set (Error 91) I've tried it with and without using the "set" in front of the X assignment, with and without declaring DIM X, alternately as Object and as Range Range("A1").Select x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate If (x Is Nothing) Then MsgBox "Not Found" Else MsgBox "x is " & x End If So, while it works okay with the "On Error Resume Next" statement inserted, I must be doing something wrong for it to generate a stop execution error...I'd like to find out what is the proper way of doing this. Can anyone give me a clue? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find woe
Thanks Norm and JE but I've tried it with
Dim x as Object and Dim x as Range and without any dim statement whatsoever I've tried it for each of the above cases with Set x = Cells.Find.... and just plain old x = Cells.Find.... all of these cases give me error 91 when the Cells.Find statement fails to find an instance of "What". Am I missing something? The only diff I see in Norm's example is that he assigns the What to a string var. And I see no diff in JE's example, unless you think it might be my variable names? Anyway....Thanks for responding you guys. I really appreciate it. Hope Microsoft fixes their Help system for Office 12. It's so f****g broken right now I want to scream whenever I try to use it. Sometimes I actually do scream. :) "Jess Wundring" wrote: I've been trying to get the following code to work in Excel 2003. It's fine if X is found. It breaks on the X assignment statement if Cells.Find fails to find anything. The error is *always*: Object variable or With block variable not set (Error 91) I've tried it with and without using the "set" in front of the X assignment, with and without declaring DIM X, alternately as Object and as Range Range("A1").Select x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate If (x Is Nothing) Then MsgBox "Not Found" Else MsgBox "x is " & x End If So, while it works okay with the "On Error Resume Next" statement inserted, I must be doing something wrong for it to generate a stop execution error...I'd like to find out what is the proper way of doing this. Can anyone give me a clue? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find woe
AHA! I just didn't see it.
I needed to eliminate the .Activate method on the Cells.Find...... THANK YOU BOTH (especially JE) for pointing this out to me. You're awesome! "Jess Wundring" wrote: I've been trying to get the following code to work in Excel 2003. It's fine if X is found. It breaks on the X assignment statement if Cells.Find fails to find anything. The error is *always*: Object variable or With block variable not set (Error 91) I've tried it with and without using the "set" in front of the X assignment, with and without declaring DIM X, alternately as Object and as Range Range("A1").Select x = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate If (x Is Nothing) Then MsgBox "Not Found" Else MsgBox "x is " & x End If So, while it works okay with the "On Error Resume Next" statement inserted, I must be doing something wrong for it to generate a stop execution error...I'd like to find out what is the proper way of doing this. Can anyone give me a clue? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Cells.find to find first number in a row which is 8000 | Excel Discussion (Misc queries) | |||
how to find cells that refer to data in other cells in excel | Excel Discussion (Misc queries) | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) | |||
If Cells.Find can't find anything | Excel Programming |