|
Error when no records meet criteria - repost
I posted this problem on 3/24/10 and received a reply that didn't work as
needed. I've pasted the original messages below. I would appreciate any help in correcting code to prevent error from occurring. Thanks in advance, Pam ____________________ Subodh, Thank you for replying. I did as you suggested and now I'm getting the same error at the next section of code where B2=5 and there are records that match this criteria. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Do you have any other suggestions that I may try? Thanks again, Pam "Subodh" wrote in message ... On Mar 25, 1:59 am, "Pam" wrote: Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" Selection.FormatConditions(2).Interior.ColorIndex = 39 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=3)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=6)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=7)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=8)" Selection.FormatConditions(3).Interior.ColorIndex = 7 End Sub Thanks in advance, Pam Try this ''''''' start of your code On error resume next ' now the code that generated the error Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 On error goto 0 ' your more code goes here End sub |
Error when no records meet criteria - repost
Try this
With Range("B2:B800") .Cells(1, 1).Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" .FormatConditions(2).Interior.ColorIndex = 39 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=OR(AND(RIGHT(D2)<""r"",OR(B2=3,B2=4)),AND(RIGHT (D2)<""p"",OR(B2=5,B2=6)),AND(RIGHT(D2)<""u"",OR (B2=7,B2=8)))" .FormatConditions(3).Interior.ColorIndex = 7 End With -- HTH Bob "Pam" wrote in message ... I posted this problem on 3/24/10 and received a reply that didn't work as needed. I've pasted the original messages below. I would appreciate any help in correcting code to prevent error from occurring. Thanks in advance, Pam ____________________ Subodh, Thank you for replying. I did as you suggested and now I'm getting the same error at the next section of code where B2=5 and there are records that match this criteria. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Do you have any other suggestions that I may try? Thanks again, Pam "Subodh" wrote in message ... On Mar 25, 1:59 am, "Pam" wrote: Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" Selection.FormatConditions(2).Interior.ColorIndex = 39 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=3)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=6)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=7)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=8)" Selection.FormatConditions(3).Interior.ColorIndex = 7 End Sub Thanks in advance, Pam Try this ''''''' start of your code On error resume next ' now the code that generated the error Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 On error goto 0 ' your more code goes here End sub |
Error when no records meet criteria - repost
Bob,
It worked!! Thank you so much. I've spent a considerable amount of time trying various forms of code to get this to work. You've saved me a lot of time in sorting and manual formatting for each section. I appreciate your help. Pam "Bob Phillips" wrote in message ... Try this With Range("B2:B800") .Cells(1, 1).Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" .FormatConditions(2).Interior.ColorIndex = 39 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=OR(AND(RIGHT(D2)<""r"",OR(B2=3,B2=4)),AND(RIGHT (D2)<""p"",OR(B2=5,B2=6)),AND(RIGHT(D2)<""u"",OR (B2=7,B2=8)))" .FormatConditions(3).Interior.ColorIndex = 7 End With -- HTH Bob "Pam" wrote in message ... I posted this problem on 3/24/10 and received a reply that didn't work as needed. I've pasted the original messages below. I would appreciate any help in correcting code to prevent error from occurring. Thanks in advance, Pam ____________________ Subodh, Thank you for replying. I did as you suggested and now I'm getting the same error at the next section of code where B2=5 and there are records that match this criteria. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Do you have any other suggestions that I may try? Thanks again, Pam "Subodh" wrote in message ... On Mar 25, 1:59 am, "Pam" wrote: Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" Selection.FormatConditions(2).Interior.ColorIndex = 39 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=3)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=6)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=7)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=8)" Selection.FormatConditions(3).Interior.ColorIndex = 7 End Sub Thanks in advance, Pam Try this ''''''' start of your code On error resume next ' now the code that generated the error Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 On error goto 0 ' your more code goes here End sub |
Error when no records meet criteria - repost
I am glad about that. I didn't spot your original email, otherwise I would
have posted there as I can see that the response you got didn't solve it. -- HTH Bob "Pam" wrote in message ... Bob, It worked!! Thank you so much. I've spent a considerable amount of time trying various forms of code to get this to work. You've saved me a lot of time in sorting and manual formatting for each section. I appreciate your help. Pam "Bob Phillips" wrote in message ... Try this With Range("B2:B800") .Cells(1, 1).Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" .FormatConditions(2).Interior.ColorIndex = 39 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=OR(AND(RIGHT(D2)<""r"",OR(B2=3,B2=4)),AND(RIGHT (D2)<""p"",OR(B2=5,B2=6)),AND(RIGHT(D2)<""u"",OR (B2=7,B2=8)))" .FormatConditions(3).Interior.ColorIndex = 7 End With -- HTH Bob "Pam" wrote in message ... I posted this problem on 3/24/10 and received a reply that didn't work as needed. I've pasted the original messages below. I would appreciate any help in correcting code to prevent error from occurring. Thanks in advance, Pam ____________________ Subodh, Thank you for replying. I did as you suggested and now I'm getting the same error at the next section of code where B2=5 and there are records that match this criteria. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Do you have any other suggestions that I may try? Thanks again, Pam "Subodh" wrote in message ... On Mar 25, 1:59 am, "Pam" wrote: Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=1)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=2)" Selection.FormatConditions(2).Interior.ColorIndex = 39 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=3)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=5)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""p"",B2=6)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=7)" Selection.FormatConditions(3).Interior.ColorIndex = 7 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""u"",B2=8)" Selection.FormatConditions(3).Interior.ColorIndex = 7 End Sub Thanks in advance, Pam Try this ''''''' start of your code On error resume next ' now the code that generated the error Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 On error goto 0 ' your more code goes here End sub |
| All times are GMT +1. The time now is 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com