Formulals

[Jahsonn]

How do I test if a range has a formula in it?

thx

 

[Frank Kabel]

Hi
use the HasFormula property and loop through the range for each cell.
Harlan Grove posted the code below. Maybe this is what you're looking
for:

Function IsFormula(r As Range) As Variant
Dim i As Long, j As Long, rv As Variant

Set r = r.Areas(1) 'since there's no way to handle multiple area
ranges

rv = r.Value

If IsArray(rv) Then
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
rv(i, j) = r.Cells(i, j).HasFormula
Next j
Next i

Else
rv = r.HasFormula

End If

IsFormula = rv
End Function

 

[Tom Ogilvy]

for multiple cells a basis for another approach would be

Dim rng as Range
On Error Resume Next
set rng = cells.specialCells(xlformulas)
On Error goto 0
if not rng is nothing
if not intersect(rng,Selection) is nothing then
if intersect(rng,Selection).Address = Selection.Address then
msgbox "Selection is all formulas"
end if
end if
End if