Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formulas List
How do I print a list of all formulas contained in a worksheet?
|
#2
|
|||
|
|||
Tools/Options, and on one of the tabs there's a box to display formulas. Click
that, do your printing, then change the setting back. The shortcut key for this is CTRL+` (the single back-quote to the left of the 1 key on the upper row of the keyboard). On Sun, 12 Dec 2004 20:05:04 -0800, "Earl" wrote: How do I print a list of all formulas contained in a worksheet? |
#3
|
|||
|
|||
"Earl" wrote...
How do I print a list of all formulas contained in a worksheet? Using a macro would be one way. Here's one that prints just formulas in both A1 and R1C1 formats from the active workbook to text files. Sub fl() Dim fd As Variant, ws AS Worksheet, c As Range fd = Freefile Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0,0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub |
#4
|
|||
|
|||
Harlan,
I found the resulting text file "somewhere", in just a Excel sub directory. I could not find any reason why it was saved in that particular directory. Would anyhow never have expected the "txt" file to be saved in an Excel directory rather than in a Word directory. This is not convenient, could you please revise the code in a way that the resulting file will be a map on screen, leaving it to me wether I like to save it somewhere or not. Jack Sons The Netherlands "Harlan Grove" schreef in bericht ... "Earl" wrote... How do I print a list of all formulas contained in a worksheet? Using a macro would be one way. Here's one that prints just formulas in both A1 and R1C1 formats from the active workbook to text files. Sub fl() Dim fd As Variant, ws AS Worksheet, c As Range fd = Freefile Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0,0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub |
#5
|
|||
|
|||
Earl
See John Walkenbach's site for his ListFormulas macro. http://j-walk.com/ss/excel/tips/tip37.htm "Most users have discovered that Excel has an option that lets you display formulas directly in their cells: Choose Tools Options, click the View tab, and select the Formulas checkbox. However, Excel doesn't provide a way to generate a concise list of all formulas in a worksheet. The VBA macro below inserts a new worksheet, then creates a list of all formulas, cell addresses and their current values." Gord Dibben Excel MVP On Sun, 12 Dec 2004 20:05:04 -0800, "Earl" wrote: How do I print a list of all formulas contained in a worksheet? |
#6
|
|||
|
|||
Harlan's line of code:
Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd used the same folder that contained the excel file. If you hadn't saved the file, it would save to the current directory of the activedrive. (Probably the Default file location if you haven't been changing things.) You could put it pretty much anywhere you wanted: Open "C:\my documents\" & activeworkbook.name & ".fl.txt" For Output As #fd (notice that .fullname was replaces with .name) JE McGimpsey does something equivalent using a worksheet: http://mcgimpsey.com/excel/listfunctions.html Jack Sons wrote: Harlan, I found the resulting text file "somewhere", in just a Excel sub directory. I could not find any reason why it was saved in that particular directory. Would anyhow never have expected the "txt" file to be saved in an Excel directory rather than in a Word directory. This is not convenient, could you please revise the code in a way that the resulting file will be a map on screen, leaving it to me wether I like to save it somewhere or not. Jack Sons The Netherlands "Harlan Grove" schreef in bericht ... "Earl" wrote... How do I print a list of all formulas contained in a worksheet? Using a macro would be one way. Here's one that prints just formulas in both A1 and R1C1 formats from the active workbook to text files. Sub fl() Dim fd As Variant, ws AS Worksheet, c As Range fd = Freefile Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0,0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub -- Dave Peterson |
#7
|
|||
|
|||
Dave,
What I want is the file with the formulas just on screen, not yet saved at all. Probably I want to look at it and most likely I do not want to save it anyhow. Is that possible? Jack. "Dave Peterson" schreef in bericht ... Harlan's line of code: Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd used the same folder that contained the excel file. If you hadn't saved the file, it would save to the current directory of the activedrive. (Probably the Default file location if you haven't been changing things.) You could put it pretty much anywhere you wanted: Open "C:\my documents\" & activeworkbook.name & ".fl.txt" For Output As #fd (notice that .fullname was replaces with .name) JE McGimpsey does something equivalent using a worksheet: http://mcgimpsey.com/excel/listfunctions.html Jack Sons wrote: Harlan, I found the resulting text file "somewhere", in just a Excel sub directory. I could not find any reason why it was saved in that particular directory. Would anyhow never have expected the "txt" file to be saved in an Excel directory rather than in a Word directory. This is not convenient, could you please revise the code in a way that the resulting file will be a map on screen, leaving it to me wether I like to save it somewhere or not. Jack Sons The Netherlands "Harlan Grove" schreef in bericht ... "Earl" wrote... How do I print a list of all formulas contained in a worksheet? Using a macro would be one way. Here's one that prints just formulas in both A1 and R1C1 formats from the active workbook to text files. Sub fl() Dim fd As Variant, ws AS Worksheet, c As Range fd = Freefile Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0,0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub -- Dave Peterson |
#8
|
|||
|
|||
Could it be done? Yeah, I bet so. But I wouldn't do it that way.
How about an alternative--just use the windows temp folder and you can clean it up when you're doing regularly scheduled housekeeping: Option Explicit Dim myTextFileName As String Sub runBoth() Call fl If Dir(myTextFileName) < "" Then Shell "start " & myTextFileName 'Application.Wait Now + TimeSerial(0, 0, 2) 'Kill myTextFileName End If End Sub Sub fl() Dim fd As Variant, ws As Worksheet, c As Range myTextFileName = Environ("temp") & "\" & ActiveWorkbook.Name & ".fl.txt" fd = FreeFile Open myTextFileName For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0, 0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub If you want to save the output, you can save it from notepad (or whatever program you have associated with .txt files.) I came back and added this: Application.Wait Now + TimeSerial(0, 0, 2) Kill myTextFileName (and commented them out.) in Win98 and using Notepad, I could delete that text file while it was open in notepad. I'm not sure if that'll work in all versions of windows or text editors. (I'm kind of surprised that it worked at all.) Jack Sons wrote: Dave, What I want is the file with the formulas just on screen, not yet saved at all. Probably I want to look at it and most likely I do not want to save it anyhow. Is that possible? Jack. "Dave Peterson" schreef in bericht ... Harlan's line of code: Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd used the same folder that contained the excel file. If you hadn't saved the file, it would save to the current directory of the activedrive. (Probably the Default file location if you haven't been changing things.) You could put it pretty much anywhere you wanted: Open "C:\my documents\" & activeworkbook.name & ".fl.txt" For Output As #fd (notice that .fullname was replaces with .name) JE McGimpsey does something equivalent using a worksheet: http://mcgimpsey.com/excel/listfunctions.html Jack Sons wrote: Harlan, I found the resulting text file "somewhere", in just a Excel sub directory. I could not find any reason why it was saved in that particular directory. Would anyhow never have expected the "txt" file to be saved in an Excel directory rather than in a Word directory. This is not convenient, could you please revise the code in a way that the resulting file will be a map on screen, leaving it to me wether I like to save it somewhere or not. Jack Sons The Netherlands "Harlan Grove" schreef in bericht ... "Earl" wrote... How do I print a list of all formulas contained in a worksheet? Using a macro would be one way. Here's one that prints just formulas in both A1 and R1C1 formats from the active workbook to text files. Sub fl() Dim fd As Variant, ws AS Worksheet, c As Range fd = Freefile Open ActiveWorkbook.Fullname & ".fl.txt" For Output As #fd On Error GoTo CleanUp For Each ws In ActiveWorkbook.Worksheets Print #fd, ws.Name For Each c In ws.UsedRange If c.HasFormula Then Print #fd, c.Address(0,0) Print #fd, "", c.Formula Print #fd, "", c.FormulaR1C1 Print #fd, "" End If Next c Print #fd, "------------" Print #fd, "" Next ws CleanUp: Close #fd End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Address List in Excel | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) | |||
Viewing List of Named Ranges | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |