VB macro

[Gerrit Van Stempvoort]

I made a VB macro for excel. I recorded the following "Find" function
and copied it into the macro:

Cells.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate

When I run the macro I got an error message:
"Error 91
Objectvariable or blockvariable With not defined."

When I replace "LookAt:=xlWhole" by "LookAt:=xlPart", then the macro
runs well, but will find the wrong cell.
What's wrong?
I don't use any "With" function in the macro.

Thanks beforehand for your help.


*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

I think you'd be better served to use a variable to represent that foundcell.
Then you could check to see if the find was successful:

Dim FoundCell as range
set foundcell = cells.find(.....) 'drop the .activate
if foundcell is nothing then
msgbox "Not found
else
msgbox "found at: " & foundcell.address
end if

===
If your find fails, then you can't activate that cell--so your original code
blows up.

 

[Tom Ogilvy]

Your getting the error because it doesn't find a cell and then tries to
activate NOTHING. To avoid that:

Dim rng as Range
set rng = Cells.Find(What:="Time", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
if rng is nothing then
msgbox "Time was not found"
else
rng.Select
msgbox "Found at " & rng.Address
End if

Make sure the cell you want to fine only contains Time - no leading or
trailing spaces or other characters. Check it with a formula

Assume it is in cell B9. In another cell
=len(B9)

If that doesn't return 4, it contains other characters.