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

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

Thank you very much.
Hans


"Peter T" <peter_t@discussions skrev i meddelelsen
...
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" wrote in message
...
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

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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

--
Rick (MVP - Excel)


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

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

Hans


"Rick Rothstein" skrev i meddelelsen
...
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

--
Rick (MVP - Excel)


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