Expose formulas

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Does anyone know how to list all the formulas in a worksheet and what cells
they are in?
Thanks in advance,
 
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
 
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
 
Thank you everyone. All your suggestions work perfectly.
Thanks again,
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
Need Macro... 8
Excel VBA 0
Excel Excel Formula OFFSET maybe? 1
Counting multiple memberships 1
Copying a formula to a range 2
Excel Moving data to sheet2 from sheet1 0
Inputting a formula with a Macro 2

Back
Top