On Fri, 14 Aug 2009 14:31:01 -0700, ker_01 wrote:

Salgud-

I responded to one of your previous posts; per my reply, I was able to get
your validation formula working by pasting it as a non-dynamic formula into
an unsed cell, then using VBA to copy/paste *that* cell to your target
(dynamic) range and letting Excel auto-modify the formula for the new target
range.

If you had problems with that approach, please post more information on
where you got stuck, and I'd be happy to try to help further.

Best,
Keith

"salgud" wrote:

I've been trying for 3 wks now to figure out a way to enter a validation
formula into the custom formula box that will both run, VBA wise, and
validate the data correctly. Basically, when I enter a name into cell, I
want it to check to see that the user entered an acceptable ID into another
cell (in the example, cell B7). The ID must be a letter followed by 6
numbers, e.g., A123456. I've made a couple of previous posts trying to get
this to work, but no one has come up with a working solution. I can't
understand why I'm having so much trouble.

I.e., the following forumla works great in a cell for testing the value:

=AND(LEN(B7)=7,NOT(ISNUMBER((LEFT(B7,1)*1))),ISNUM BER(RIGHT(B7,6)*1))

When I drop the = sign (why do some formulas require and = sign in the
custom validation box and others not?) and put it in the custom validation
formula box, it gives me a FALSE on a valid ID. Any ideas why? I've had the
same problem with other custom validation formulas, which makes me think
I'm missing something important in how they work and/or how to apply them.
I'm new to validation, though I've been doing VBA for a while. I'd
appreciate any help in getting this straigtened out.


Thanks for your reply.

I saw your previous post, but I don't want the validation formulae in
"target cells", I have no problem doing that. What I want is the forumlae
in the Custom field in the Validation box. Also, I don't understand how
entering the formula into GG1, then copying it to another cell, then
clearing the contents of both would get the formula into the Validation
Custom field. Maybe if you explained in more detail, I could follow it.