HasFormula

[HK]

Bob Phillips and maybe many others have provided a UDF like

Function HASFORMULA(rng As Range)
If rng.Count = 1 Then
HASFORMULA = rng.HASFORMULA
Else
HASFORMULA = CVErr(xlErrRef)
End If
End Function

Can this function be changed to show an array of TRUE/FALSE values
reflecting whether cells in a range holds formulas or not?

Hans Knudsen

 

[Peter T]

Function GOTFORMULA(rng As Range)
' array enter with Ctr-Shift Enter
Dim arr()
On Error GoTo errH
With rng
ReDim arr(1 To .Rows.Count, 1 To .Columns.Count)
For C = 1 To UBound(arr, 2)
For r = 1 To UBound(arr)
arr(r, C) = .Cells(r, C).HasFormula
Next
Next
End With
GOTFORMULA = arr
Exit Function
errH:
GOTFORMULA = CVErr(xlErrRef)
End Function

Regards,
Peter T

 

[HK]

Thank you very much.
Hans

Function GOTFORMULA(rng As Range)
' array enter with Ctr-Shift Enter
Dim arr()
On Error GoTo errH
With rng
ReDim arr(1 To .Rows.Count, 1 To .Columns.Count)
For C = 1 To UBound(arr, 2)
For r = 1 To UBound(arr)
arr(r, C) = .Cells(r, C).HasFormula
Next
Next
End With
GOTFORMULA = arr
Exit Function
errH:
GOTFORMULA = CVErr(xlErrRef)
End Function

Regards,
Peter T

 

[Peter T]

I should of course have declared ALL the variables

Function GOTFORMULA(rng As Range)
' array enter with Ctr-Shift Enter
Dim r As Long, c As long
Dim arr()

Peter T

 

[Rick Rothstein]

It is unclear how you intend to use the function you asked for. Would it be
enough if the function simply told you if every cell in the specified range
hold formulas or not? This function will return TRUE if every cell in the
range holds a formula and FALSE otherwise...

Function RangeHasFormulas(Rng As Range) As Boolean
Dim R As Range
For Each R In Rng
If Not R.HasFormula Then Exit Function
Next
RangeHasFormulas = True
End Function

 

[HK]

To Rick Rothstein
Peter T's function does exactly what I wanted.

Hans