HasFormula() vs. has a "numeric" entry

[mark kubicki]

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

 

[Chip Pearson]

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

 

[witek]

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

 

[Dana DeLouis]

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

 

[Joerg]

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

 

[keepITcool]

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.

 

[Chip Pearson]

Very good point.

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