Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 -------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Crashes when typing "False" in VLookup formula | Excel Worksheet Functions | |||
Excel crashes when typing "false" in VLookup function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel crashes on "Close" Visual Basic error 400 | Excel Discussion (Misc queries) | |||
Excel 2000 crashes after "touching" a userform with the mouse and closing it | Excel Programming |