Expose formulas
 

Does anyone know how to list all the formulas in a worksheet and what cells
they are in?
Thanks in advance,

Expose formulas
 

Hi Lee

The below macro will identify all formulas from Sheet1 and list out in
Sheet2..with cell address and the formula in text format..Adjust to suit. Try
and feedback

Sub Mac()
Dim lngRow As Long: longRow = 1
For Each cell In Sheets("Sheet1").UsedRange
If Left(cell.Formula, 1) = "=" Then
lngRow = lngRow + 1
Sheets("Sheet2").Range("A" & lngRow) = cell.Address
Sheets("Sheet2").Range("B" & lngRow) = "'" & cell.Formula
End If
Next
End Sub



--
If this post helps click Yes
---------------
Jacob Skaria


"Lee" wrote:

Does anyone know how to list all the formulas in a worksheet and what cells
they are in?
Thanks in advance,

Expose formulas
 

hi
another way would be to use the keyboard shortcut Ctrl + ~ .
this would allow you to toggle from normal view to formula view and back.

Regards
FSt1

"Lee" wrote:

Does anyone know how to list all the formulas in a worksheet and what cells
they are in?
Thanks in advance,

Expose formulas
 

If Left(cell.Formula, 1) = "=" Then

Another possibility (a simpler one) for this line would be...

If cell.HasFormula Then

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Hi Lee

The below macro will identify all formulas from Sheet1 and list out in
Sheet2..with cell address and the formula in text format..Adjust to suit.
Try
and feedback

Sub Mac()
Dim lngRow As Long: longRow = 1
For Each cell In Sheets("Sheet1").UsedRange
If Left(cell.Formula, 1) = "=" Then
lngRow = lngRow + 1
Sheets("Sheet2").Range("A" & lngRow) = cell.Address
Sheets("Sheet2").Range("B" & lngRow) = "'" & cell.Formula
End If
Next
End Sub



--
If this post helps click Yes
---------------
Jacob Skaria


"Lee" wrote:

Does anyone know how to list all the formulas in a worksheet and what
cells
they are in?
Thanks in advance,

Expose formulas
 

Thank you everyone. All your suggestions work perfectly.
Thanks again,

"FSt1" wrote:

hi
another way would be to use the keyboard shortcut Ctrl + ~ .
this would allow you to toggle from normal view to formula view and back.

Regards
FSt1

"Lee" wrote:

Does anyone know how to list all the formulas in a worksheet and what cells
they are in?
Thanks in advance,