Find vba problem

T

teeb

I'm using the following to find text in cells:

Sub Find()
Cells.Find(What:=Range("D3").Value, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub

The formula works if it only text in the cell. What if the text is the
same only as a result of the formula in the cell, the above doesn'
work

I take it there is no match as above is looking at the formula in th
cell and not the text? Is there any way it can to changed to recognis
the text and not the formula
 
T

teeb

Thanks I tried your suggestion, I'm now getting an error;

Object variable or With block variable not set

Any further ideas
 
D

Dave Peterson

It sounds like what you're looking for wasn't found.

the common approach:

dim Foundcell as range
set foundcell = Cells.Find(What:=Range("D3").Value, After:=ActiveCell, _
LookIn:=xlvalues, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:= False)

if foundcell is nothing then
msgbox "it wasn't found
else
msgbox "found it at: " & foundcell.address
end if
 
J

JE McGimpsey

One reason you could get that error is that if Find doesn't find a
match, it returns #Nothing#. Your code then tries to activate #Nothing#,
which causes an error.

I'm confused as to why this is happening in your code, since the
function should obviously find (at least) cell D3 (it does in my test
workbook. In general, you should avoid the problem by trapping for
#Nothing#:

Public Sub Find()
Dim rFound As Range
Set rFound = Cells.Find( _
What:=Range("D3").Value, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:= False)
If Not rFound Is Nothing Then
rFound.Activate
Else
MsgBox Range("D3").Value & " not found."
End If
End Sub

Note that it's almost always a bad idea to use a reserved VBA word (like
Find) as a variable or procedure name - it often ends up causing
confusion either in the programmer or in the code logic.

Note also that activation/selection is almost never necessary - working
with the range objects directly makes your code smaller, faster, and,
IMO, easier to maintain.

Post back with your code if you want help with using the range objects
directly...
 
Top