Convert Specific Cells to Values

[ptmuldoon]

I want to be able to convert all formulas to values except for various total lines.

Is there a way to do that without looping cell by cell? I can do it with a loop, but if I try to use a With statement it seems much faster, but it converts everything.

Please Login or Register  to view this content.

[AlphaFrog]

I don't know the layout of your data. It may be faster to convert all formulas to values and then re-establish the SUM formulas. Where are the sum formulas located?

If the totals are all at say the bottom of each column, it would be easy to exclude them from the conversion. This example excludes the last row...

Please Login or Register  to view this content.

[ptmuldoon]

Unfortunately, the totals will be scattered around the worksheet. Essentially taking a set of Financial Statements where I have totals and subtotals for Revenue, Expenses, Difference columns, etc for an Income Statement, Balance Sheet and other reports.

I can do it with the loop, just trying to find a faster way.

[romperstomper]

You'll have to loop, unless there is some pattern to the locations of the totals. You can load all the formulas into an array, process that and then put them back which should be faster than replacing cell by cell.

[AlphaFrog]

This would loop through only the cells with formulas and exclude blanks and constants in the loop.

Please Login or Register  to view this content.

[mjr veverka]

Another variant:

Please Login or Register  to view this content.