Problem with changing a cell validation through VBA

Dear all,

I have a problem with a macro which I have recorded in Excel. The macro
comes with an error in running the recorded script. This error is:
Run-time error '1004': Application-defined of object-defined error.

This is the VBA-coding which has the problem:

     Range("A12").Validation _
    .Modify xlValidateCustom,
Formula1:=3D"=3DAND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUCTS;3;F=
=ADALSE)=3D""RED"")"


Momentarily the cell has the same validation, only the Vlookup has to
match "YELLOW". This rule has been manually entered and works correct.
When the cell validation is changed manually into "RED", there is also
no problem.

Only the funny part is, when I record this change and re-run it, VBA
shows the above mentioned error.

I have been trying to change this little script, but I still did not
manage to make it run. Can someone help me with this issue?

Who can help me on this subject?
Thanks in advance.

0
Edebruin9 (5)
9/22/2006 9:26:54 AM
excel 39879 articles. 2 followers. Follow

5 Replies
651 Views

Similar Articles

[PageSpeed] 42

Try using a comma instead of a semicolon in your formula1 expression.  VBA is
pretty USA centric.

Remco wrote:
> 
> Dear all,
> 
> I have a problem with a macro which I have recorded in Excel. The macro
> comes with an error in running the recorded script. This error is:
> Run-time error '1004': Application-defined of object-defined error.
> 
> This is the VBA-coding which has the problem:
> 
>      Range("A12").Validation _
>     .Modify xlValidateCustom,
> Formula1:="=AND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUCTS;3;F�ALSE)=""RED"")"
> 
> Momentarily the cell has the same validation, only the Vlookup has to
> match "YELLOW". This rule has been manually entered and works correct.
> When the cell validation is changed manually into "RED", there is also
> no problem.
> 
> Only the funny part is, when I record this change and re-run it, VBA
> shows the above mentioned error.
> 
> I have been trying to change this little script, but I still did not
> manage to make it run. Can someone help me with this issue?
> 
> Who can help me on this subject?
> Thanks in advance.

-- 

Dave Peterson
0
petersod (12005)
9/22/2006 12:11:28 PM
Dave,

I have altered the statement as you suggested. It does not solve the
problem.
More ideas?

Thanks in advance.

Dave Peterson wrote:
> Try using a comma instead of a semicolon in your formula1 expression.  VB=
A is
> pretty USA centric.
>
> Remco wrote:
> >
> > Dear all,
> >
> > I have a problem with a macro which I have recorded in Excel. The macro
> > comes with an error in running the recorded script. This error is:
> > Run-time error '1004': Application-defined of object-defined error.
> >
> > This is the VBA-coding which has the problem:
> >
> >      Range("A12").Validation _
> >     .Modify xlValidateCustom,
> > Formula1:=3D"=3DAND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUCTS;3=
;F=ADALSE)=3D""RED"")"
> >
> > Momentarily the cell has the same validation, only the Vlookup has to
> > match "YELLOW". This rule has been manually entered and works correct.
> > When the cell validation is changed manually into "RED", there is also
> > no problem.
> >
> > Only the funny part is, when I record this change and re-run it, VBA
> > shows the above mentioned error.
> >
> > I have been trying to change this little script, but I still did not
> > manage to make it run. Can someone help me with this issue?
> >
> > Who can help me on this subject?
> > Thanks in advance.
>=20
> --=20
>=20
> Dave Peterson

0
Edebruin9 (5)
9/22/2006 12:27:39 PM
You sure you got all the semicolons changed to commas?

Since you're using .modify, does that cell have existing Data|validiation rules?



Remco wrote:
> 
> Dave,
> 
> I have altered the statement as you suggested. It does not solve the
> problem.
> More ideas?
> 
> Thanks in advance.
> 
> Dave Peterson wrote:
> > Try using a comma instead of a semicolon in your formula1 expression.  VBA is
> > pretty USA centric.
> >
> > Remco wrote:
> > >
> > > Dear all,
> > >
> > > I have a problem with a macro which I have recorded in Excel. The macro
> > > comes with an error in running the recorded script. This error is:
> > > Run-time error '1004': Application-defined of object-defined error.
> > >
> > > This is the VBA-coding which has the problem:
> > >
> > >      Range("A12").Validation _
> > >     .Modify xlValidateCustom,
> > > Formula1:="=AND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUCTS;3;F�ALSE)=""RED"")"
> > >
> > > Momentarily the cell has the same validation, only the Vlookup has to
> > > match "YELLOW". This rule has been manually entered and works correct.
> > > When the cell validation is changed manually into "RED", there is also
> > > no problem.
> > >
> > > Only the funny part is, when I record this change and re-run it, VBA
> > > shows the above mentioned error.
> > >
> > > I have been trying to change this little script, but I still did not
> > > manage to make it run. Can someone help me with this issue?
> > >
> > > Who can help me on this subject?
> > > Thanks in advance.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
9/22/2006 12:41:53 PM
Dave,

Yes, we have changed all semicolons to commas. And yes, we did have a
validation-rule on the cell. The only thing is that this validation was
entered manually, instead of through VBA.


Dave Peterson wrote:
> You sure you got all the semicolons changed to commas?
>
> Since you're using .modify, does that cell have existing Data|validiation=
 rules?
>
>
>
> Remco wrote:
> >
> > Dave,
> >
> > I have altered the statement as you suggested. It does not solve the
> > problem.
> > More ideas?
> >
> > Thanks in advance.
> >
> > Dave Peterson wrote:
> > > Try using a comma instead of a semicolon in your formula1 expression.=
  VBA is
> > > pretty USA centric.
> > >
> > > Remco wrote:
> > > >
> > > > Dear all,
> > > >
> > > > I have a problem with a macro which I have recorded in Excel. The m=
acro
> > > > comes with an error in running the recorded script. This error is:
> > > > Run-time error '1004': Application-defined of object-defined error.
> > > >
> > > > This is the VBA-coding which has the problem:
> > > >
> > > >      Range("A12").Validation _
> > > >     .Modify xlValidateCustom,
> > > > Formula1:=3D"=3DAND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUC=
TS;3;F=ADALSE)=3D""RED"")"
> > > >
> > > > Momentarily the cell has the same validation, only the Vlookup has =
to
> > > > match "YELLOW". This rule has been manually entered and works corre=
ct.
> > > > When the cell validation is changed manually into "RED", there is a=
lso
> > > > no problem.
> > > >
> > > > Only the funny part is, when I record this change and re-run it, VBA
> > > > shows the above mentioned error.
> > > >
> > > > I have been trying to change this little script, but I still did not
> > > > manage to make it run. Can someone help me with this issue?
> > > >
> > > > Who can help me on this subject?
> > > > Thanks in advance.
> > >
> > > --
> > >
> > > Dave Peterson
>=20
> --=20
>=20
> Dave Peterson

0
Edebruin9 (5)
9/22/2006 1:38:28 PM
I don't have a guess.  It worked when I used commas and replaced the existing
rule.

Remco wrote:
> 
> Dave,
> 
> Yes, we have changed all semicolons to commas. And yes, we did have a
> validation-rule on the cell. The only thing is that this validation was
> entered manually, instead of through VBA.
> 
> Dave Peterson wrote:
> > You sure you got all the semicolons changed to commas?
> >
> > Since you're using .modify, does that cell have existing Data|validiation rules?
> >
> >
> >
> > Remco wrote:
> > >
> > > Dave,
> > >
> > > I have altered the statement as you suggested. It does not solve the
> > > problem.
> > > More ideas?
> > >
> > > Thanks in advance.
> > >
> > > Dave Peterson wrote:
> > > > Try using a comma instead of a semicolon in your formula1 expression.  VBA is
> > > > pretty USA centric.
> > > >
> > > > Remco wrote:
> > > > >
> > > > > Dear all,
> > > > >
> > > > > I have a problem with a macro which I have recorded in Excel. The macro
> > > > > comes with an error in running the recorded script. This error is:
> > > > > Run-time error '1004': Application-defined of object-defined error.
> > > > >
> > > > > This is the VBA-coding which has the problem:
> > > > >
> > > > >      Range("A12").Validation _
> > > > >     .Modify xlValidateCustom,
> > > > > Formula1:="=AND(COUNTIF($A$11:$A$52;$A12)<2;VLOOKUP($A12;PRODUCTS;3;F�ALSE)=""RED"")"
> > > > >
> > > > > Momentarily the cell has the same validation, only the Vlookup has to
> > > > > match "YELLOW". This rule has been manually entered and works correct.
> > > > > When the cell validation is changed manually into "RED", there is also
> > > > > no problem.
> > > > >
> > > > > Only the funny part is, when I record this change and re-run it, VBA
> > > > > shows the above mentioned error.
> > > > >
> > > > > I have been trying to change this little script, but I still did not
> > > > > manage to make it run. Can someone help me with this issue?
> > > > >
> > > > > Who can help me on this subject?
> > > > > Thanks in advance.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
9/22/2006 3:01:48 PM
Reply:

Similar Artilces:

New Disk storage
Hi, I'm planning on adding a new SAN to our Exchange system (Active/Passive 2 node cluster) and this has been connected to the Exchange cluster nodes and is accessible as shared disk storage. But that isn't the question I've got, basically the mail stores are currently located on a Powervault (Drive S:) and I need to present a plan for moving this data (14 databases) onto the SAN storage, I can see two options Option1) Use the ESM to migrate databases to new drive letter 1. Use exmerge to export all email to PST files (and ensure backup) 2. use the ESM utility to change the...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

multiple Domain name delivery problem
hi, I currently have Exchange Server 2003 Build 7638:2 SP2,. We have multiple domain names being delivered to the exchange store. I have nothad any problems, but i currently have one user that is not receiving emails with attachments from one certain "internet" sender to one of her email addresses, but the other address works fine & if they send emails without attachments, everything works fine from either address. I have had the user send them message with attachments to me & the user with the problem & i get the message, but not the other user! I even use message...

Exchange update problem
I have tried to upgrade exch2k3 sp1 to sp2, but the update fails with "the file pcproxy.dll is in use, and setup cannot identify the app or srvc. setup cannot continue" Any clues/ideas/suggestions? Please. -- ----------------------------------------------------------------------------------------------------------------------- This message has been checked for all known viruses. The information contained in this e-mail and any attachments is confidential and may be the subject of legal, professional or other privilege. It is intended for the named addressee only and may not ...

How do you change the background color of a picture?
I have a image that I have copy and pasted, however I want to fill behind the image. I know how to fill and all that, my problem is that it recognizes the entire image as a picture, I was wondering if there is a way to change the background while the image lays on top? sureisdifferent wrote: > I have a image that I have copy and pasted, however I want to fill > behind the image. I know how to fill and all that, my problem is that > it recognizes the entire image as a picture, I was wondering if there > is a way to change the background while the image lays on top? =============...

Sort ascending, make changes, restore previous order
I've got an AutoFilter in a spreadsheet. I want to sort ascending, mak some changes to some cells, then restore the previous order. Can thi be done easily or will this require some programming?? Thanks in advane! Matt -- BVHi ----------------------------------------------------------------------- BVHis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=859 View this thread: http://www.excelforum.com/showthread.php?threadid=47508 I'd use a helper column. Put =row() and drag down. Convert it to values (edit|copy, edit|paste special|values) Do all y...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

SQL deadlock problem
I am currently having a big issue with sql deadlocking on the PrincipalObjectAccess table. The last few months I have been working on a synchronization process using a Biztalk orchestration. The sync uses the crm webservices to create and update account and contact records in CRM. But now deployment to the production environment gives me some problems. It seems that when trying to update account records (which is one of the first actions in the sync process) the webservice gives me Generic SQL errors and SQL timeouts. After extensive profiling and tracing in SQL I found that there are...

docmd.transfertext problem
Hi, I am using access 97 and tried to import a csv file to the mdb table. I run a code as following: DoCmd.TransferText acImportDelim, "Specification4", "input", DEFAULT_PATH & "online.txt", 1 In online.txt, there is a field which is 10 digit number and I specified it as a double datatype in the specification4. After the import, I found out that the 10 digit number data in the field get empty in the destined table while other fields are all right. Therefore I import manually using specification4 instead of running code. This time the 1...

what's the formula for adding symbols in cells?
I have a chart that has blank info in the legend. I want to add an * to indicate something, but just inserting a symbol doesn't work. Any ideas? Thanks. Debi - To add information to the legend, you need to add to a series name. Right click on the chart, select Source Data from the pop up menu, click on the series tab, select a series, and either type something in the name box, or click in it and select a cell with the mouse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Debi wrote: > I have a chart tha...

How can I change 'Normal' Style for Word e-mails to 'Normal (Web)'?
Hi, I'm using Word as my e-mail editor in Outlook 2003 and want to change the default Style for e-mails from 'Normal' to 'Normal (Web)'. The problem is that new e-mails and replies in HTML format use the 'Normal' Microsoft Word 'Style', and this has no gap after paragraphs. The upshot of this is that when sending an e-mail, I have to press return twice to create a gap, but when the recipient views this, their software shows it as four gaps (the extra carriage return I typed + their correctly viewed HTML carriage return after each line). E.g. I type this: ...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Strange Access Denied Problem with Windows 7
I got a new computer about six months ago that came with Windows Vista Home Premium 64bit. Before that I had done all of my .NET development either on an XP Pro VM or my former XP Pro computer at home. Shortly after getting my new computer at home, I also got a license for VMWare to be able to test my software on multiple platforms and configurations. I had wrote an application originally in VB.NET that was a simple backup utility. It supports mutiple backup configurations. Any given copnfiguration would define a backup which would be a list of files to backup, a list of folders to ...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...

SOAP Exception
Hi, In my C# code, I have defined a Map object which is sent as part of a request to a web service, here's an extract of the part of the code: - Map map = new Map(); map.item = new mapItem[4] { new mapItem(), new mapItem(), new mapItem(), new mapItem() }; ................. But when I submit the request containing the Map object, the compiler throws an error with the following message:- org.xml.sax.SAXException: No deserializer for {http://xml.apache.org/ xml-soap}...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

how do i change colour of scroll bar in worksheet
I find it very difficult to see the scroll bar in my excel workshhet as they are white, can they be changed to a colour and if so how ? Are the scrollbars white in other applications? In win98, I could change some display settings, but I couldn't change the scrollbar colors. But if the scrollbars aren't white in other applications, then this can't be the solution. Jayne wrote: > > I find it very difficult to see the scroll bar in my excel workshhet as they > are white, can they be changed to a colour and if so how ? -- Dave Peterson In word they are, but not on a...

Border problems
Not sure why all of a sudden all my borders in my tables created with Publisher can only be white. No other color will show when selected. Opening a pub file done on another computer where the borders show color, shows white only. I have attempted to do a repair on publisher, which gave no help. Have attempted to uninstalled and reinstall Publisher without clearing the problem. Anyone have any ideas or suggestions? Look in the Accessibility Options in the control panel, display tab, disable "use high contrast." If that doesn't solve the issue, read the third FAQ here....

Can you only merge up to a certain number of cells
I am working on microsoft excel 2003, I have a sheet that I merged cells starting with line 8 through 43...when I type my information in the merged area I can see all that I am typing...say it goes up to line 30 once I hit the enter key I can only see up to line 20. Even when I print it out it only prints up to line 20...I have checked to make sure there are not locked cells etc. I cannot figure out at all why this is happening...is there only up to a certain number of cells you can merge? From "Excel Specifications and Limits" Length of cell contents (text) ...

Validation #7
I want to restrict data entry to a cell to be only "Y" or "N". Using Validation I can achieve this, Stopping any other numbers or letters, but not apparently symbols. "*" is still allowable. Any ideas anyone? PWS Not if you use allow>list, in the list source box put Y,N and uncheck in cell dropdown -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Paul W Smith" <pws@twelve.me.uk> wrote in message news:oYMsc.8289$NK4.971637@stones.force9.net... > I want to restrict data entry to a cell to b...

Question about Cell Borders
I need to be able to set a default cell border style, thickness and color, such that when the Border button on the Format toolbar is clicked, that border will be consistently applied to the selected cells. Is there any way to permanently change the border produced by the toolbar button. Thanks Kevin I don't know of any way to reliably do what you're after. However, a better solution would be to create a new toolbar button and attach a macro that you record (Tools/Macros/Record new macro) applying the border style, thickness and color that you want. In article <C4FC8DC5-C...

How do I copy data in single cell format to a merged cell format
I need to copy a list of data in single cell format into a spreadsheet where those same columns are merged cells (with four cells merged into one). I have several hundred lines of data so some way of copy & paste (other than each cell individually) would be appreciated. Thanks Paul Hi Paul What I would do is copy format of sheet with merge cells to blank sheet then copy single cell data to sheet with merge cells select single cell to paste you lose formatting so after just copy format from blank sheet created earlier I find this easiest way Hope this helps Tina "Paul" w...

Outlook 2002 XP SP 3 Archive problem
Hi there I am trying to get my newly configured outlook to archive my folders. I have ust configured it to download two POP email accounts and that works fine. I have set up a set of folders below my outlook (Personal Folders / pst) inbox node and placed the relevant emails in the corresponding folders. I have gone into tools -> options -> other -> and pressed the "AutoArchive..." button -> and set the "clean out folders older than" selection boxes to "3" and "Months" and pressed the "apply these setting to all folders" button. I ...