Error when no records meet criteria

P

Pam

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
 
S

Subodh

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
Click to expand...

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
 
P

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
Click to expand...

Do you have any other suggestions that I may try?
Thanks again,
Pam


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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error when no records meet criteria - repost 3
More than 4 conditional formating? 14
Conditional Format Syntax Error 2
alternate rowshading using conditional format excel 2007/2010 6
VBA Conditional formating 3
conditional formatting in excel vba 1
4 conditional formats 2
conditional format based on column statistics and loop over columns 1

Top