How can I distinguish if a cell has formula entry (ex: =sum(A1:A25) ) vs. a
simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString, 1) = "="

thanks in advance,
-mark

Try

Dim F As String
Dim S As String

F = ActiveCell.Formula
S = Application.ConvertFormula(F, xlA1, xlR1C1)
If F = S Then
Debug.Print "No cell references"
Else
Debug.Print "cell references"
End If




"mark kubicki" wrote in message
...
How can I distinguish if a cell has formula entry (ex:
=sum(A1:A25) ) vs. a simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString,
1) = "="

thanks in advance,
-mark

what about formulas which use named ranges.
like
=MyRange +1
?


Chip Pearson wrote:
Try

Dim F As String
Dim S As String

F = ActiveCell.Formula
S = Application.ConvertFormula(F, xlA1, xlR1C1)
If F = S Then
Debug.Print "No cell references"
Else
Debug.Print "cell references"
End If




"mark kubicki" wrote in message
...

How can I distinguish if a cell has formula entry (ex:
=sum(A1:A25) ) vs. a simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString,
1) = "="

thanks in advance,
-mark

what about formulas which use named ranges.

Maybe one could include something like this:

Dim nPrecedents As Long

On Error Resume Next 'In case there are none
nPrecedents = ActiveCell.Precedents.Count
If n 0 Then
' There are cell references...
End If

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"witek" wrote in message
...
what about formulas which use named ranges.
like
=MyRange +1
?


Chip Pearson wrote:
Try

Dim F As String
Dim S As String

F = ActiveCell.Formula
S = Application.ConvertFormula(F, xlA1, xlR1C1)
If F = S Then
Debug.Print "No cell references"
Else
Debug.Print "cell references"
End If




"mark kubicki" wrote in message
...

How can I distinguish if a cell has formula entry (ex: =sum(A1:A25) ) vs.
a simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString, 1) = "="

thanks in advance,
-mark

"Dana DeLouis" wrote in message
...
what about formulas which use named ranges.

Maybe one could include something like this:

Dim nPrecedents As Long

On Error Resume Next 'In case there are none
nPrecedents = ActiveCell.Precedents.Count
If n 0 Then
' There are cell references...
End If
--
HTH. :)
Dana DeLouis


Maybe I missed something, but why not::

if ActiveCell.HasFormula then
'has formula with or without cell references (example for 'without
reference': =10+5)
else
'no formula
end if


Joerg

Dana,

precedents EXCLUDE references to cells other than activesheet.

[sheet1!a1].formula = "=sheet2!a1"
msgbox [sheet1!a1].precedents.count '<No CellsFound

Testing .Formula < .Value appears similar to .HasFormula
surprisingly it is 3x faster.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dana DeLouis wrote in

what about formulas which use named ranges.

Maybe one could include something like this:

Dim nPrecedents As Long

On Error Resume Next 'In case there are none
nPrecedents = ActiveCell.Precedents.Count
If n 0 Then
' There are cell references...
End If

Very good point.


"witek" wrote in message
...
what about formulas which use named ranges.
like
=MyRange +1
?


Chip Pearson wrote:
Try

Dim F As String
Dim S As String

F = ActiveCell.Formula
S = Application.ConvertFormula(F, xlA1, xlR1C1)
If F = S Then
Debug.Print "No cell references"
Else
Debug.Print "cell references"
End If




"mark kubicki" wrote in message
...

How can I distinguish if a cell has formula entry (ex:
=sum(A1:A25) ) vs. a simple "numeric entry (ex: 123)?

-would the most efficient way be to check if Right(AnyString,
1) = "="

thanks in advance,
-mark