VBA Problem

T

TomD

I have this code as part of a "Show Winners" macro
button..........

Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Which, if Ladies not found (because button activated
without any entries being made in spreadsheet), gives....

Runtime error 91
Object variable or With Block varibale not set.

Now, take same code, replace "Ladies" with either "1"
or "2" (infact, the same code for finding "1" and "2"
precedes that for the "Ladies", and runtime error does
not occur. The error only exists when it hits the code
for ladies and Ladies is not present ??????? If 1 or 2
not present no error ?????

How come and how do I resolve.???

Thanks

Tom
 
M

Melanie Breden

Hi Tom,
I have this code as part of a "Show Winners" macro
button..........

Cells.Find(What:="Ladies", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Which, if Ladies not found (because button activated
without any entries being made in spreadsheet), gives....

Runtime error 91
Object variable or With Block varibale not set.
Click to expand...

try this:

Dim rngFind As Range

Set rngFind = _
Cells.Find(What:="Ladies", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If Not rngFind Is Nothing Then rngFind.Select

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
T

TomD

Melanie,

Following on from previous message, I found the ideal
solution..........

On Error Resume Next

It may not be elegant and there is an underlying runtime
error but the result is as required.

Regards

Tom
 
Top