Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error when no records meet criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 99
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count unique records that also meet criteria Brigette Excel Discussion (Misc queries) 3 December 3rd 09 05:27 PM
How can I get DGET to return all records that meet the criteria? Eddie A Excel Worksheet Functions 1 July 30th 08 07:25 AM
count records which meet filter criteria AKH Excel Discussion (Misc queries) 1 October 31st 05 03:54 PM
How do I show number of records that meet criteria filter CliffD Excel Discussion (Misc queries) 3 August 22nd 05 01:10 PM
Extracting records that meet multiple criteria Cindy Excel Programming 1 November 27th 03 04:57 AM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement