"If Not.." crashes
 

I have a macro which tells Excel to run through a list of cells
and print a worksheet if the content of the cell satisfies two
conditions. The first one is that the content of the cell should
match the content of a cell named "Restrict". This works.

The other condition is that the cell should not contain a question
mark. I can't get this to work. The macro works fine if I let it
look for a "?" and then print if it finds one - but I want the
opposite, cells with "?" in them should be ignored.

This is the code that works:

If Left(ActiveCell, Len(Range("Restrict"))) = Range("Restrict") _
Then
If InStr(1, ActiveCell, "?") Then

[Code for print-out]

End If
End If


I've tried to amend the second line like this:

If Not(InStr(1, ActiveCell, "?")) Then

and

If InStr(1, ActiveCell, "?") = 0 Then

but when I try to run the macro, Excel freezes up before
finishing.

What am I doing wrong here??

"If Not.." crashes
 

Hi


not sure of your issue here - but a couple of points to
bear in mind.

Instr is not a logical function, but rather returns a
poasition - so the use of NOT is not valid (unless you
follow it with a logical condition).

Also bear in mind that instr will return a null sometimes
(but not from a cell I think).

Are you sure your code stops at this logical statement -
step through to isolate the condition(s) that cuases your
lockup.


ttfn benm

ttfn benm

"If Not.." crashes
 

Ben,

thanks for the clarification about Instr. The macro appears to crash
Excel after the first print-out. But if I can't use NOT (or = 0 ..?)
with InStr, I'll need another way to tell Excel to skip cells with a
"?" in them.

Richard


"Ben McBen" wrote in
:

Instr is not a logical function, but rather returns a
poasition - so the use of NOT is not valid (unless you
follow it with a logical condition).

Also bear in mind that instr will return a null sometimes
(but not from a cell I think).

Are you sure your code stops at this logical statement -
step through to isolate the condition(s) that cuases your
lockup.

"If Not.." crashes
 

Your "= 0" should be fine - I suspect your crash is
elsewhere - have you step through to see where the lockup
happens (this is always tricky for me because I get too
optimistic and F8 away too quickly and miss the actual
culprit)


ttfn benm

"If Not.." crashes
 

If InStr(1, ActiveCell, "?") = 0 Then
should be the correct statement.

As for excel freezing, it has to do with your rest of the code and not
above statement.
The only difference if "?" found and not found is that in former case is
sure that the cell is not empty in later case even for empty cells the
condition meets.
So may be if the cell is empty, your code creates problem.

You can modify the condtion as below and try:

If (Not IsEmpty(ActiveCell)) AND InStr(1, ActiveCell, "?") = 0 Then

or check the code if it will crash if the cell is empty.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"If Not.." crashes
 

Ben -

thank you for your prompt follow-up. I'm back from work, and have
tested further.

I tried to find out where the crash occurred, but it was difficult
to pinpoint as I was thrown out and didn't have the patience to
write down every step I took.

And I still was puzzled that the

If InStr(1, ActiveCell, "?") Then

worked, while the thing crashed when I added the "= 0". From
Sharad's response I had a feeling that my "?" might have been
interpreted as "any digit/letter", not literally as a question
mark - so I added a parameter to define the cell content as text:

If InStr(1, ActiveCell, "?", 1) = 0 Then

- and believe it or not, now it seems to work! Is this weird or as
expected?

Richard


"Ben McBen" wrote in
:

Your "= 0" should be fine - I suspect your crash is
elsewhere - have you step through to see where the lockup
happens (this is always tricky for me because I get too
optimistic and F8 away too quickly and miss the actual
culprit)


ttfn benm

"If Not.." crashes
 

Sharad -

thank you for your advice. As I've related in my reply to Ben, I
tried to find out where the crash occurred, but it was difficult
to pinpoint. But from your response I gathered that my "?" might
have been interpreted as "any digit/letter", not literally as a
question mark - so I added the parameter "1" to the InStr
statement to define the cell content as text:

If InStr(1, ActiveCell, "?", 1) = 0 Then

- and now the macro actually seems to work! If it crashes on me
again, I'll be back ...

Richard


Sharad wrote in
:

If InStr(1, ActiveCell, "?") = 0 Then
should be the correct statement.

As for excel freezing, it has to do with your rest of the code
and not above statement.
The only difference if "?" found and not found is that in former
case is sure that the cell is not empty in later case even for
empty cells the condition meets.
So may be if the cell is empty, your code creates problem.

You can modify the condtion as below and try:

If (Not IsEmpty(ActiveCell)) AND InStr(1, ActiveCell, "?") = 0
Then

or check the code if it will crash if the cell is empty.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"If Not.." crashes
 

Both of the below work for me.

Regards,
KL (XL2K)
--------------------------
Sub test()
If Not InStr(ActiveCell, "?") 0 Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub
--------------------------
Sub test()
If InStr(ActiveCell, "?") = 0 Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub
--------------------------


"Richard H" wrote in message
9...
Ben -

thank you for your prompt follow-up. I'm back from work, and have
tested further.

I tried to find out where the crash occurred, but it was difficult
to pinpoint as I was thrown out and didn't have the patience to
write down every step I took.

And I still was puzzled that the

If InStr(1, ActiveCell, "?") Then

worked, while the thing crashed when I added the "= 0". From
Sharad's response I had a feeling that my "?" might have been
interpreted as "any digit/letter", not literally as a question
mark - so I added a parameter to define the cell content as text:

If InStr(1, ActiveCell, "?", 1) = 0 Then

- and believe it or not, now it seems to work! Is this weird or as
expected?

Richard


"Ben McBen" wrote in
:

Your "= 0" should be fine - I suspect your crash is
elsewhere - have you step through to see where the lockup
happens (this is always tricky for me because I get too
optimistic and F8 away too quickly and miss the actual
culprit)


ttfn benm

"If Not.." crashes
 

KL -

yeah, they work for me too in a new workbook, even though the "1"
parameter isn't included in the InStr command. So it's strange
that the addition of this parameter apparantly had an effect,
especially as Ben and Sharad are convinced that the error had
nothing to do with the InStr command. I'll have to test further.
Thank you!

Richard


"KL" <lapink2000(at)hotmail.com wrote in news:eZ#qCLu3EHA.2540
@TK2MSFTNGP09.phx.gbl:

Both of the below work for me.

Regards,
KL (XL2K)
--------------------------
Sub test()
If Not InStr(ActiveCell, "?") 0 Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub
--------------------------
Sub test()
If InStr(ActiveCell, "?") = 0 Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub
--------------------------