Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expose formulas
Does anyone know how to list all the formulas in a worksheet and what cells
they are in? Thanks in advance, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Expose SeriesCollection? | Excel Programming | |||
expose excel spreadsheet in web page | Excel Programming | |||
VBA to expose details of zip files | Excel Programming | |||
Expose macros with add-in | Excel Programming | |||
How can I expose the actual column & row headers in excel? | Excel Discussion (Misc queries) |