Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error when no records meet criteria - repost
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 - repost
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

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default Error when no records meet criteria - repost
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


--

HTH

Bob

"Pam" wrote in message
...
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

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default Error when no records meet criteria - repost
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




"Bob Phillips" wrote in message
...
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


--

HTH

Bob

"Pam" wrote in message
...
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

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default Error when no records meet criteria - repost
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.

--

HTH

Bob

"Pam" wrote in message
...
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




"Bob Phillips" wrote in message
...
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


--

HTH

Bob

"Pam" wrote in message
...
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

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
Error when no records meet criteria Pam[_3_] Excel Programming 2 March 25th 10 01:36 PM
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
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 09:17 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