Formulals
 

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

thx

Formulals
 

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


--
Regards
Frank Kabel
Frankfurt, Germany

"Jahsonn" schrieb im Newsbeitrag
...
How do I test if a range has a formula in it?

thx

Formulals
 

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

--
Regards,
Tom Ogilvy


"Frank Kabel" wrote in message
...
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


--
Regards
Frank Kabel
Frankfurt, Germany

"Jahsonn" schrieb im Newsbeitrag
...
How do I test if a range has a formula in it?

thx