Finding Formulas in Workbooks

ExcelBoston

New Member
Joined
Oct 28, 2009
Messages
2
Hi,

I need to search over 400 Excel workbooks in over 90 sub-folders for the existence of formulas entered in cells. These specific workbooks are not supposed to have any formulas in them so I’m trying to easily find any exceptions.
<o:p> </o:p>
How can I do this and either pipe the output into a text file or display it in a spreadsheet?
<o:p> </o:p>
I’d appreciate any help!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
A couple questions, first, what good would it do you to list all the formulas; potentially there could be tens of thousands. What would you do with that list.

It seems that if those workbooks are not supposed to have any formulas in them, then as long as all the workbooks are being evaluated for the existence of formulas, why not just programmatically convert all the worksheets' used ranges to values and that would fix the problem...

...unless, the reason for keeping the formulas and listing them is pure curiosity to see which workbooks and worksheets and cell addresses contain formulas. Nothing wrong with that but cognitively that could be a lot of info where someone says "so what" and you'd presumably need to deal with the existence of those formulas anyway by copying for paste special values.

The second question is, what version of Excel are you using, and what version(s) of Excel are those workbooks saved as, which can potentially influence the syntax of code to do what you ultimately need to do.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Wouldn't selecting the whole sheet and using Copy/Paste Values achieve this in one strike?
 

ExcelBoston

New Member
Joined
Oct 28, 2009
Messages
2
These Excel Workbooks (remember there are over 400 of them in 90 folders) are used for Software Testing. The Spreadsheets are set up using what is called the KeyWord (or Action Word) paradigm. Essentially, the spreadsheets are test protocols used in the testing of a very large application. We have over 5000 tests that are run automatically using these spreadsheets. Some of the spreadsheets have only 500 rows of test statements where a large spreadsheet will have perhaps 7,000 rows.

Because of regulatory requirements the protocols must not have any formulas (they must be deterministic) at the time of execution.

So, I'm tasked with finding any exceptions to this mandate. It is not reasonable to find them manually so I need a script or macro to do the work.

How to do so?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top