PDA

View Full Version : Extract all workbooks cell names and formulas in a spreadsheet



valexalex
03-12-2009, 02:37 AM
Hi,


Does somebody uses a vba Sub for extracting all celsnames, formulas and results from the work book and list them in a spreadsheet like
CellName , = formula, result


Thanks

Bob Phillips
03-12-2009, 03:12 AM
What do you mean by cell name, A2 or defined names?

Maybe something like (untested)



For Each cell In ActiveSheet.UsedRange

If cell.HasFormula Then

NextRow = NextRow + 1
Worksheets("Results").Cells(NextRow, "A").Value = cell.Address(False, False)
Worksheets("Results").Cells(NextRow, "B").Value = "'" & cell.Formula
Worksheets("Results").Cells(NextRow, "C").Value = cell.Value
End If
End With

Jan Karel Pieterse
03-12-2009, 03:26 AM
DOwnload my name manager to create a list of names:
www.jkp-ads.com/officemarketplacenm-en.asp (http://www.jkp-ads.com/officemarketplacenm-en.asp)

valexalex
03-12-2009, 05:01 AM
Sorry for specifying it not clear. I mean defined names in WorkbookNames

Bob Phillips
03-12-2009, 05:27 AM
And what if a cell has a formula, but is not part of a defined name? What about multi-cell defined names?

valexalex
03-12-2009, 08:10 AM
Seems if the cell has a formula but not a name, the left part of the suggested format should be free.
If the cell has a formula, but doesn't nave a name there will be nothing in the middle area of the.
Not sure about the range names :(