What function can be used to determine whether a cell contains a formula?

Thanks in advance for your help.

Roger

Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


€žRoger€� ezt Ã*rta:

What function can be used to determine whether a cell contains a formula?

Thanks in advance for your help.

Roger

A couple of sites which should help you

"Identify formulas using Conditional Formatting "


http://www.j-walk.com/ss/excel/usertips/tip045.htm

http://www.pcmag.com/article2/0,1759,1573749,00.asp

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567403

Stefi,

cellref.HasFormula

built-in property of a range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


"Roger" ezt írta:

What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger

Bob:

Thanks for your input. I tried Stefi's suggestion, but somehow I can't get
it to work. It may be because I don't know much about macros.

Basically, I have two columns containing formulas. Any of the two columns
can be overwritten with data. In one of the two columns which was not
overwritten with data, I would like it to be changed to write-protected. In
the third column, I want to know which of the first two columns still has the
formula.

How to achieve this? Thanks again in advance.

Regards,
Roger



"Bob Phillips" wrote:

Stefi,

cellref.HasFormula

built-in property of a range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


"Roger" ezt Ã*rta:

What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger

Add the UDF to a code module and then in the worksheet use

=IF(IsFormula(M1),"M",IF(IsFormula(N1),"N",""))

where I assume M and N are those two columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger" wrote in message
...
Bob:

Thanks for your input. I tried Stefi's suggestion, but somehow I can't
get
it to work. It may be because I don't know much about macros.

Basically, I have two columns containing formulas. Any of the two columns
can be overwritten with data. In one of the two columns which was not
overwritten with data, I would like it to be changed to write-protected.
In
the third column, I want to know which of the first two columns still has
the
formula.

How to achieve this? Thanks again in advance.

Regards,
Roger



"Bob Phillips" wrote:

Stefi,

cellref.HasFormula

built-in property of a range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


"Roger" ezt írta:

What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger

Bob:

I understand the part you have mentioned. I am having some problem with
Stefi's below-mentioned UDF. Can you please write the UDF again? Thanks.

Regards,
Roger



"Bob Phillips" wrote:

Add the UDF to a code module and then in the worksheet use

=IF(IsFormula(M1),"M",IF(IsFormula(N1),"N",""))

where I assume M and N are those two columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger" wrote in message
...
Bob:

Thanks for your input. I tried Stefi's suggestion, but somehow I can't
get
it to work. It may be because I don't know much about macros.

Basically, I have two columns containing formulas. Any of the two columns
can be overwritten with data. In one of the two columns which was not
overwritten with data, I would like it to be changed to write-protected.
In
the third column, I want to know which of the first two columns still has
the
formula.

How to achieve this? Thanks again in advance.

Regards,
Roger



"Bob Phillips" wrote:

Stefi,

cellref.HasFormula

built-in property of a range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


"Roger" ezt Ã*rta:

What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger

I use this one:

In a General Module:

Option Explicit
Function IsFormula(rng As Range)
IsFormula = rng(1).HasFormula
End Function



Roger wrote:

Bob:

I understand the part you have mentioned. I am having some problem with
Stefi's below-mentioned UDF. Can you please write the UDF again? Thanks.

Regards,
Roger

"Bob Phillips" wrote:

Add the UDF to a code module and then in the worksheet use

=IF(IsFormula(M1),"M",IF(IsFormula(N1),"N",""))

where I assume M and N are those two columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger" wrote in message
...
Bob:

Thanks for your input. I tried Stefi's suggestion, but somehow I can't
get
it to work. It may be because I don't know much about macros.

Basically, I have two columns containing formulas. Any of the two columns
can be overwritten with data. In one of the two columns which was not
overwritten with data, I would like it to be changed to write-protected.
In
the third column, I want to know which of the first two columns still has
the
formula.

How to achieve this? Thanks again in advance.

Regards,
Roger



"Bob Phillips" wrote:

Stefi,

cellref.HasFormula

built-in property of a range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stefi" wrote in message
...
Try this UDF:
Function IsFormula(cellref)
IsFormula = Left(Range(cellref).Formula, 1) = "="
End Function

Regards,
Stefi


"Roger" ezt Ã*rta:

What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger







--

Dave Peterson

Roger Wrote:
What function can be used to determine whether a cell contains a
formula?

Thanks in advance for your help.

Roger

I picked up this from Jwalk.com and thought it was pretty neat.


Code:
--------------------
Sub QuickMap()
If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
' Create object variables for cell subsets
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells _
(xlFormulas, xlNumbers + xlTextValues + xlLogical)
Set TextCells = Range("A1").SpecialCells(xlConstants, xlTextValues)
Set NumberCells = Range("A1").SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0

' Add a new sheet and format it
Sheets.Add
With Cells
.ColumnWidth = 2
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With

Application.ScreenUpdating = False

' Do the formula cells
If Not IsEmpty(FormulaCells) Then
For Each Area In FormulaCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "F"
.Interior.ColorIndex = 3
End With
Next Area
End If

' Do the text cells
If Not IsEmpty(TextCells) Then
For Each Area In TextCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "T"
.Interior.ColorIndex = 4
End With
Next Area
End If

' Do the numeric cells
If Not IsEmpty(NumberCells) Then
For Each Area In NumberCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "N"
.Interior.ColorIndex = 6
End With
Next Area
End If
End Sub



--------------------


He offers it up as a tip but I think it's also included in his utility
pack.


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=567403