Problem with changing a cell validation through VBA

[Remco]

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]

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

 

[Remco]

Dave,

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

Thanks in advance.

 

[Dave Peterson]

You sure you got all the semicolons changed to commas?

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

 

[Remco]

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]

I don't have a guess. It worked when I used commas and replaced the existing
rule.

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.