|
Error when no records meet criteria
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 |
Error when no records meet criteria
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
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 11:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com