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 


0
Pam
3/24/2010 8:39:27 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
996 Views

Similar Articles

[PageSpeed] 0

On Mar 25, 1:59=A0am, "Pam" <pamnos...@deltaprocess.com> wrote:
> Hi,
>
> I have the following code and receive error 1004 Application defined or
> object defined error on this line
>
> Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""r"",B2=3D4)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> because there are no records in this instance of the report for "4" in Co=
lB.
> I've searched and applied code all to no avail. =A0Can someone please tel=
l me
> how to write code for when this may occur in any of the situations below?
>
> Range("b2:b800").Select
> =A0 =A0 Selection.FormatConditions.Delete
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""u"",B2=3D1)"
> =A0 =A0 Selection.FormatConditions(1).Interior.ColorIndex =3D 4
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""u"",B2=3D2)"
> =A0 =A0 Selection.FormatConditions(2).Interior.ColorIndex =3D 39
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""r"",B2=3D3)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""r"",B2=3D4)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""p"",B2=3D5)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""p"",B2=3D6)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""u"",B2=3D7)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 7
>
> =A0 =A0 Selection.FormatConditions.Add Type:=3DxlExpression, Formula1:=3D=
 _
> =A0 =A0 =A0 =A0 "=3DAND(RIGHT(D2)<>""u"",B2=3D8)"
> =A0 =A0 Selection.FormatConditions(3).Interior.ColorIndex =3D 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:=3DxlExpression, Formula1:=3D _
     "=3DAND(RIGHT(D2)<>""r"",B2=3D4)"
     Selection.FormatConditions(3).Interior.ColorIndex =3D 7
 On error goto 0
' your more code goes here
End sub
0
Subodh
3/24/2010 11:36:12 PM
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" <getsubodh@gmail.com> wrote in message 
news:3d88a8b5-aa0d-49dc-a844-840e7cd5859e@s2g2000prd.googlegroups.com...
On Mar 25, 1:59 am, "Pam" <pamnos...@deltaprocess.com> 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 


0
Pam
3/25/2010 1:15:28 PM
Reply:

Similar Artilces:

Recorded macro: What happens if I change filename?
Hi everyone, Say I have a recorded macro named "Base" and linked it to a button in a an excel workbook named ABC.xls Later, say I changed the file name to DEF.xls Now, when I click on the button to execute, I get "out of range" error and the file close! How can I make the macro name is independent of the file name; so it doesn't keep referring to the old file name? Regards, Mike Hi Mike two questions, 1) where are you saving the macro - is it in the ABC workbook or in your personal workbook? - if you want the macro to be available to a number of different w...

suppressing #VALUE! error message in Excel
Can'r solve the problem in my formulas when #VALUE! message appears in the cell after entering the formula in formula bar. Tried everything - cell size of merged cells, formate, etc. Template constructed in Excel used for income property analysis. Happy to share sample with all formulas being used. ...

Error enabling users in 4.0
Hi, Just upgraded to version CRM 4.0 on a test server using the 90 day trial key and everything went well. Yesterday the Gold Partner Enterprise key arrived and I upgraded the license. I went into user management and tried to enable some of the users I had disabled during test. Got the error below. Has anyone else experienced this? Thanks, Manso [SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.SystemUserPrincipals' with unique index 'cndx_SystemUserPrincipals'. Cannot insert duplicate key row in object 'dbo.SystemUserPrincipals' with ...

Microsoft visual C++ Runtime Error #2
When I open Outlook 2000, the first time I ger the following error "RUNTIME ERROR PROGRAM:C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\OUTLOOK.EXE" It says this application has requested the runtime to terminate it in an unusual way. Please contact the applications support team for more information. There are two of us on the outlook e-mail that we share. We have two profiles and sepaarate PAB & PST files. I get it the first time, and when I close it out, it will open on the second go around with no problem. When I open the other profile, I get it the first time I open it, the...

Office 2010 Install error.
Hello All, I beg your indulgence. I am new here, albeit not new to the MS and computing world. I am having an odd issue and cannot seem to find my way out. Recently dl'd the Office 2010 from Technet. I have 10 machines, all newer and quite capable of running Office. I have installed it under Win7Ult. successfully on a few machines, and the rub, one a few others the setup fails with the exact same error. It follows: Error 1935. An error occurred during the installation of assembly component {9DA4DC8A-9731-FC17CA6848AD}, HRESULT: Ox800736B3. Setup failed. Rolling back cha...

user define message error
Hi! I created a SDI app and I want to show a dialog after creating SDI, therefore I defined an user define message #define WMU_STARTUP (WM_APP+10) and call it from CSDIView::OnInitUpdate() with PostMessage(WM_STARTUP). In debug version it work correctly but in release version it generates an Application Error after close the program. what's the problem? Thankx ...

runtime error '429'
I have an access 2000 program that is running on sevral machines and working fine. On one machine XP, Office2K the program gives the error: runtime error 429, Active x Component can't create object. This error occurs on the line "Set Fso....." Dim fso As New FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") I have tried an office Repair, office upgrade, Reinstalling office, I have the the following references installed. Visual Basic for Applications Microsoft Access 9.0 for Object Library OLE Automation Microsoft DAO 3.6 Object Library...

OLE File error
I have publisher 2003 and am trying to save my newsletter in 2000 format for my printer (a printing company) to read and publish. My error says an OLE object is open and the souce file needs to be closed before saving it to 2000 format. I have looked the whole letter over and I don't have any embedded objects like a database, or spreadsheet in the newsletter. I'm stuck as to what to do to save it to the earlier version. Anyone can call toll free with advise or post to the group or email me at decaturjewelry2@insightbb.com 1-888-877-4367 thanks Lew Does the publication have WordAr...

Error saving Excel files in a network drive
I have a problem saving Excel files onto a network drive. I get an error saying it was imposible to save the file. It creates a temporary file and then I have to open it and save it as a new document. This issue doesn�t occur saving the file in my hard disk. This happens with "Full control" access to the shared folder... I have Windows XP and Office 2000. Thanks in advance Mateo. When excel saves the file, it saves it as a temporary file with a funny name (8 characters--no extension). If the save is successful, xl will delete the original and if that's successful, xl will re...

"The messaging interface has returned an unknown error" #2
One of my users keeps seeing this popup on an intermittent basis when they try to open an attachment. I've tried what I can find on the web about this with no luck. So I'm looking for more suggestions... The environment is a Windows Server 2003 Terminal Services environment supporting about 20 users. Only one other user has reported this, but he is unable to reproduce it. Is there a way to get Outlook to log more information about why this is happening? -- Hal Berenson, President PredictableIT, LLC I should add Outlook 2003 SP1 with all Office Update patches applied. -...

Database Errors and Can't Install service pack
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel 1. I upgraded to OSX 10.6 2. When I load Word or Excel or Power Point I get "There is a problem with the Database" 3. I follow the steps to do a rebuild. It tells me its done but the moving bar only gets about 80% of the way to the right and freezes. 4. This does not fix the problem 5. I check for updates, the Service Pack 12.1 comes up and looks for a long time for my hard drive (searching for additional disks)! 6. When I choose my hard drive, I get the following message. "Office 2008 SP1 Update (12....

exchanging meeting/calendar requests between Excahnge 5.5 and Exchange 2000?
Hi All I have EXchange 5.5 in one of our outside organizations (only connected to use through VPN), and have Exchange 2000 here locally. Is there a way to at the very least, be able to send/recieve/reply to calendar metting requests between these two organizations? Thanks, Jack ...

EXCEL ERRORS
I have just installed Excel 2003 as part of the 2003 Offoce package, but when I start Excel, I get the following message: Compile Error in hidden module:AutoExecNew By clicking on it, the program seems to work normally. Also, when I close, I get the following message: Compile Error in hidden module:DistMon Also the program closes automatically when I click the OK in the dialog box. However - It is rather annoying. Anyone know of a way to get rid of these messages? Thanks in advance http://support.microsoft.com/?id=307410 http://support.microsoft.com/default.aspx?scid=kb;EN-US;q30741...

"Error accesing file. Network connection may have been lost."
Hello everyone, How can I clear this message that I am getting everytime that I try to compile. "Error accesing file. Network connection may have been lost." Thanks in advance, JM JM, if you are not actually connecting to databases over the network, and you are using an older (unpatched) version of Access 2000, you may have run into this nasty bug: Error Message: Error Accessing File. Network Connection May Have Been Lost at: http://allenbrowne.com/ser-23a.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.co...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

JCB.DLL Not Found Error
I get the error described in article http://support.microsoft.com/default.aspx?scid=kb;en-us;273087 Callback DLL not found Unable to find the callback library jcb.dll (or one of its dependencies). Copy in the file and hit OK to retry, or hit Cancel to abort. It happens exactly as explained here except I have JCB.DLL in the correct folder and even moved it to c:\windows\system32 and it still doesn't work. I've tried copying a new file from a different server and even off the SP1 CD and I keep getting this error no matter what I do. This is happening on all servers in the enter...

How can i Disappear a Blank Record ?
hi, I want ask you for a problem I am dealing with is the following one: In a continuous form, when the user clicks on the “add new record” button, the form adds a new blank record. When the user moves the cursor in another record of the list, without filling the previously mentioned “new record”, this “new record” remains in the list. How can I make the “new record” - which remains blank – disappear from the list? Another question also: Can I use the command “bookmark” and how? I would appreciate a prompt reply. Thank you in advance for your attention. A form has the "new record&...

Returning cell value if 2 criteria are met
I'm struggling with a SUMPRODUCT I have two worksheets (WS1 and WS2) both with the following columns: 'Material Number' (Column A) and 'Site' (Column B). They each have around 9000 rows. In WS2 there is another value, 'Group' (Column C) If the a Material and Site combination from WS1 are found in WS2, return the Group in WS2:Column C to Column C in WS1. Any ideas? =SUMPRODUCT(--(WS2!$A$2:$A$10000=A2),--(WS2!$B$2:$B$10000=B2),--(WS2!$C$2:$C$10000)) If you merely select A:A, it chockes. Must be a range. "D Zandveld" wrote: > I'm strugglin...

error/please help
I formatted my PC & did a clean install of Windows Xp PRO when i opened Excell i get this error message. Microsoft visual basic COMPILE ERROR IN VISUAL MODULE:AUTO EXEC NEWThe help files tell me to unprotect,BUT there is no unprotect option in tools/protection. I uninstalled Excel,bebooted & installed Excell. Still get the error ...

help with Schema validation erroring out every single element
Hi: The problem I am having is when I validate an xml file to a schema, it is erroring out every element. I think this has something to do with me defining/referencing the namespaces. I have searched on the net for a while, but am still confused. Thanks in advance for help. Below is the code I am working with. What am I not referencing/referencing incorrectly? =================================== Output when trying to validate: Message: The element 'urn:PeopleQuery:query' has invalid child element 'urn:PeopleQuery:match'. Expected 'match'. An error occurred ...

Missing 512 records in a query
I was setting up a query that had been troublesome so I was pulling each element of the query separately to locate which of 5 related tables was causing the problem. I forgot to enter a criteria in the first element of the query so it should have retrieved the entire table. The table contains 24100 records but the query only retrieved 23588. A little investigation found a few of the missing records and I can find them in the table but they don't appear in the query. Why don't they appear in the query? What have I done wrong that a query can't see these records they look fin...

Filter criteria from another cell?
I'm doing spreadsheets with built-in filters accessible through buttons/macros. The advanced filters use criteria fields to determine what data to filter, i.e., a column header with a < or> and the number in the cell beneath it. I also have than number elsewhere in the spreadsheet with a more "user-friendly" label. Is there a way to get the filter criteria to refer to the other cell and apply a "greater than" or "less than" to that number? In other words, the filter criteria would point to the value in the other cell and apply a < or > to that valu...

Exchange 2000 default website errors
I have a relatively new Exchange 2000 server that I put online this past fall and moved all of my 5.5 mailboxes and info store to it. Anyway, it's running well, but when I look in the IIS manager under default website, I see errors with Public, Exchange and Exadmin - red stop signs. I also see a mapped network drive (M:) that seems connected to my company's internet domain name - not sure why that's there? Any advice? Thanks, Mark ...

4.0 Client Config error
The Client config returns an error with this in the server log; Exception information: Exception type: HttpParseException Exception message: Could not create type 'Microsoft.Crm.Sdk.Crm2007.CrmDiscoveryServiceAD'. Request information: Request URL: http://toshiba2002:5555/MSCRMServices/2007/AD/CrmDiscoveryService.asmx Request path: /MSCRMServices/2007/AD/CrmDiscoveryService.asmx The web client works OK, and I also get this same error when I try to connect the DMManager. The Server is SBE. Any ideas? -- Thanx Grizz Dear see this http://support.micros...

Outlook Error When Sending
Hi, I'm getting the following error more and more when trying to send email or reply to email I've received. I'm using a POP3 account, and it's becoming a real pain. Any idea what's causing this or how it can be resolved? Thanks in advand for any help you can offer. Michael The message could not be sent because one of the recipients was rejected by the server. The rejected e- mail address was 'mhl38@msn.com'. Subject 'Re: Iowa Caucus Party!', Account: 'Michael', Server: 'mail.coloradofordean.org', Protocol: SMTP, Server Respons...