Determining if Cell has Formula

[PosseJohn]

I have a worksheet that contains formulas that are linked dynamically to an
outside source. I have created a Workbook_BeforeClose routine that asks the
user if formulas should be removed from the worksheet (to lock in the values
from outside source).

If the formulas have already been removed, I don't want to ask the user
about formulas that have already been removed.

How can I determine if a specific cell contains a formula or an actual value?

 

[Gary Keramidas]

maybe one of these:

If ActiveCell.HasFormula = True Then
MsgBox ActiveCell.Address & " contains a formula"
End If


If Range("A1").HasFormula = True Then
MsgBox Range("A1").Address & " contains a formula"
End If

 

[PosseJohn]

Thank you Gary, couldn't recall the 'HasFormula' property. That was exactly
the answer I needed.

Have a great Turkey day!

 

[Peter T]

Another way if you don't want to loop all cells looking for formulas

Sub test()
Dim rng As Range
Dim ws As Worksheet

Set ws = ActiveSheet
' or say
' Set ws = ActiveWorkbook.Worksheets("Sheet1")

On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
MsgBox rng.Count & " formula cells" & vbCr & _
"address of first formula " & rng(1).Address(0, 0), , _
rng.Parent.Name
End If

End Sub

Regards,
Peter T