Problem with changing a cell validation through VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Remco - 22 Sep 2006 10:26 GMT

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;FALSE)=""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.

Reply to this Message

Dave Peterson - 22 Sep 2006 13:11 GMT

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

> Dear all,
>
[quoted text clipped - 21 lines]
> Who can help me on this subject?
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Remco - 22 Sep 2006 13:27 GMT

Dave,

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

Thanks in advance.

> Try using a comma instead of a semicolon in your formula1 expression. VBA is
> pretty USA centric.
[quoted text clipped - 24 lines]
> > Who can help me on this subject?
> > Thanks in advance.

Reply to this Message

Dave Peterson - 22 Sep 2006 13:41 GMT

You sure you got all the semicolons changed to commas?

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

> Dave,
>
[quoted text clipped - 36 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Remco - 22 Sep 2006 14:38 GMT

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.

> You sure you got all the semicolons changed to commas?
>
[quoted text clipped - 40 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 22 Sep 2006 16:01 GMT

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

> Dave,
>
[quoted text clipped - 50 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message