Error when no records meet criteria - repost

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

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
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
 
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
 
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
 
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.
 
Back
Top