VBA Problem
 

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

VBA Problem
 

Hi Tom,

TomD wrote:
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.

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)

VBA Problem
 

Melanie,

Thanks for your reply. I'm not a programmer per se but
use the record macro facility of excel to do what I want
plus I do some limited and simple VBA coding to enhance.

I've been down the route you have suggested but come
upto a brick wall when refining and making code more
efficient. Another MVP has been extremely helpful in
this (Dick Kusleika) but we are at the stage whereby if
the column contains physical (e.g. typed input) values of
1 or 2 or Ladies then OK but if it contains these values
from cell formula (which is the case) then these values
are never found. I/we don't know why.

I'm therefore reverting back to "simplistic" recorded code
which brings up issue I described - and which I started
with Again, don't know why I get an error searching for
the text Ladies in a column but not when searching for a
Number such as 1 or 2. Is it as simple as numeric -v-
text ?? If so how do I overcome?? Using recorded
mechanism seem to negate whether physical or formula
values

What I'm doing is searching a column (700 rows), looking
for the first 3 instances each of the number 1, number 2
and the word Ladies. The macro has thus been recorded 3
times. I copy each occurance to separate sheet.

The only problem is if there are no Ladies ????????
That's when I get the error; otherwise works fine.

Regards

Tom


-----Original Message-----
Hi Tom,

TomD wrote:
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.

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)

.

VBA Problem
 

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

-----Original Message-----
Melanie,

Thanks for your reply. I'm not a programmer per se but
use the record macro facility of excel to do what I want
plus I do some limited and simple VBA coding to enhance.

I've been down the route you have suggested but come
upto a brick wall when refining and making code more
efficient. Another MVP has been extremely helpful in
this (Dick Kusleika) but we are at the stage whereby if
the column contains physical (e.g. typed input) values
of
1 or 2 or Ladies then OK but if it contains these values
from cell formula (which is the case) then these values
are never found. I/we don't know why.

I'm therefore reverting back to "simplistic" recorded
code
which brings up issue I described - and which I started
with Again, don't know why I get an error searching for
the text Ladies in a column but not when searching for a
Number such as 1 or 2. Is it as simple as numeric -v-
text ?? If so how do I overcome?? Using recorded
mechanism seem to negate whether physical or formula
values

What I'm doing is searching a column (700 rows), looking
for the first 3 instances each of the number 1, number 2
and the word Ladies. The macro has thus been recorded 3
times. I copy each occurance to separate sheet.

The only problem is if there are no Ladies ????????
That's when I get the error; otherwise works fine.

Regards

Tom


-----Original Message-----
Hi Tom,

TomD wrote:
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.

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)

.

.