UDF & Named Range error Excel 2007

  • Thread starter Thread starter HarryisTrying
  • Start date Start date
H

HarryisTrying

I have created a user defined function called IsIPAddress and it works fine
if I put =IsIPAdress(a2) in column B, for example. It also works if I use
data validation, settings, Allow and select custom and put in the formula
IsIPAddress. If I put an equals sign in it doesn't work =IsIPAddress

I am also trying to see if there are duplicates so I have tried the following:
=AND(COUNTIF($A$2:$A$2000,A2),=1,IsIPAddress)

if I do the compound equation above or the =IsIPAdress I get the following
error "A named range you specified cannot be found".

Why is it looking for a name range when I have a UDF with that name? Is
there a way to prevent this error?

If I put the =IsIPAddress in an adjacent cell B2 (for example) the IP check
works great. Then I put this in the formula using the Validation Icon and it
works,
=and(countif($a$2:$a:2000,a2)<=1,b2)

I would like to have one compound formula using the Validation Icon but
can't seem to figure this out.
 
One more item. to test if the optional parameter exits use IsMissing

Function IsIPAdress(a1,optional a2)
if ismissing(IsIPAdress) then

else

end if

end if
 
I made a typo


Function IsIPAdress(a1,optional a2)
if ismissing(a2) then

else

end if

end function

IsIPAdress
 
I have created a user defined function called IsIPAddress and it works fine
if I put =IsIPAdress(a2) in column B, for example. It also works if I use
data validation, settings, Allow and select custom and put in the formula
IsIPAddress. If I put an equals sign in it doesn't work =IsIPAddress

I am also trying to see if there are duplicates so I have tried the following:
=AND(COUNTIF($A$2:$A$2000,A2),=1,IsIPAddress)

if I do the compound equation above or the =IsIPAdress I get the following
error "A named range you specified cannot be found".

Why is it looking for a name range when I have a UDF with that name? Is
there a way to prevent this error?

If I put the =IsIPAddress in an adjacent cell B2 (for example) the IP check
works great. Then I put this in the formula using the Validation Icon and it
works,
=and(countif($a$2:$a:2000,a2)<=1,b2)

I would like to have one compound formula using the Validation Icon but
can't seem to figure this out.
Click to expand...

You are omitting the argument to your UDF.

=IsIPAddress

is not the same as

=IsIPAddress(cell_ref)

--ron
 
Ooops, I typed it wrong in my post but I did include the argument in the Data
Validation formula.. I get the Named Range error. Temporarily I put the
IsIPAddress test in another column (D) and then put this formual in and it
works fine.
=AND(COUNTIF($A$2:$A$2000,A2),=1,(D2))
 
Back
Top