Error when no records meet criteria

  • Thread starter Thread starter Pam
  • Start date Start date
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
 
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
 
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
 
Back
Top