I have a range of cells containing formulas. I'm trying to write a UDF that
will search the range and return either TRUE or FALSE if one or more cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Bob,

That formula just checks a cell for a formula, so it can only return True or
False. Deleting a formula doesn't force a #VALUE.

Do you mean if they delete the row or column containing the cell? If so, the
worksheet formula changes to =IsFormula(#REF), so there is not much you can
do other than correct the formula, or use =isformula(INDIRECT("A1")), but it
will point to the new A1 if the original A1 gets deleted.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Bob" wrote in message
...
I have a range of cells containing formulas. I'm trying to write a UDF
that
will search the range and return either TRUE or FALSE if one or more cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Bob,
No, the users are not deleting a row or column . . . they are simply
inadvertently deleting the formula in one or more cells within a range. Is
there a way to modify my UDF so that it will return either TRUE or FALSE if
one or more formulas are missing in a RANGE?
Thanks, Bob


"Bob Phillips" wrote:

Bob,

That formula just checks a cell for a formula, so it can only return True or
False. Deleting a formula doesn't force a #VALUE.

Do you mean if they delete the row or column containing the cell? If so, the
worksheet formula changes to =IsFormula(#REF), so there is not much you can
do other than correct the formula, or use =isformula(INDIRECT("A1")), but it
will point to the new A1 if the original A1 gets deleted.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Bob" wrote in message
...
I have a range of cells containing formulas. I'm trying to write a UDF
that
will search the range and return either TRUE or FALSE if one or more cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Function IsFormula(rng As Range) As Boolean
IsFormula = False
For Each r In rng
If Not r.HasFormula Then
Exit Function
End If
Next
IsFormula = True
End Function

--
Gary's Student


"Bob" wrote:

I have a range of cells containing formulas. I'm trying to write a UDF that
will search the range and return either TRUE or FALSE if one or more cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Thanks!!! Please forgive me for imposing, but instead of showing either TRUE
or FALSE, could you kindly tell me how to modify your UDF to show the COUNT
of missing formulas?
Thanks again, Bob


"Gary''s Student" wrote:

Function IsFormula(rng As Range) As Boolean
IsFormula = False
For Each r In rng
If Not r.HasFormula Then
Exit Function
End If
Next
IsFormula = True
End Function

--
Gary's Student


"Bob" wrote:

I have a range of cells containing formulas. I'm trying to write a UDF that
will search the range and return either TRUE or FALSE if one or more cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Function IsFormula(rng As Range) As Long
For Each r In rng
If Not r.HasFormula Then
IsFormula = IsFormula + 1
End If
Next
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Gary''s Student" wrote in message
...
Function IsFormula(rng As Range) As Boolean
IsFormula = False
For Each r In rng
If Not r.HasFormula Then
Exit Function
End If
Next
IsFormula = True
End Function

--
Gary's Student


"Bob" wrote:

I have a range of cells containing formulas. I'm trying to write a UDF
that
will search the range and return either TRUE or FALSE if one or more
cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I
would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob

Bob - Thanks!!!


"Bob Phillips" wrote:

Function IsFormula(rng As Range) As Long
For Each r In rng
If Not r.HasFormula Then
IsFormula = IsFormula + 1
End If
Next
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Gary''s Student" wrote in message
...
Function IsFormula(rng As Range) As Boolean
IsFormula = False
For Each r In rng
If Not r.HasFormula Then
Exit Function
End If
Next
IsFormula = True
End Function

--
Gary's Student


"Bob" wrote:

I have a range of cells containing formulas. I'm trying to write a UDF
that
will search the range and return either TRUE or FALSE if one or more
cells
are missing the formula (due to someone inadvertently deleting them):

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

Unfortunately, the UDF returns #VALUE! when a formula is missing. I
would
appreciate any help in fixing the aforementioned UDF.
Thanks, Bob