Paste certain formulas as formulas and others as values to a new workbook

[fokk]

Hello,

I'm currently working on an old project that collects data from our accounting software and exports it to a file in excel that contains formatting and formulas for budgeting.

The problem i'm facing is that if you transfer this file from our remote desktop environment, the formulas no longer work since they are a reference to a macro that utilizes a db connection.

What i have tried to do is make a script that copies some set ranges to paste values and others to paste the formulas into a new workbook. This have proven to be difficult if we want to add lines to the budget itself then we have to add lines to the module too.

Another problem is since the file contains so much information, i have to set the ranges really low to be able to copy without encountering errors.

I'm currently trying to make a two dimensional array that scans each sheet for cells containing formulas. Copying the text information is working, however i'm not able to figure out how to do a check to see if a cell contains a certain formula.

Is there an other way to do this which is smarter?

For reference; the formulas i want to seperate are either SUM-formulas or IF-formulas with a long string behind them.

Sincerely

[fokk]

I think i'm on to solving it using a double for searching through the sheets and a function:

Please Login or Register  to view this content.

However, its very slow. Any tips on how to speed it up, or another solution that would work better?

[fokk]

Nevermind. This doesn't work. Apparently i'm not able to search the formula for text. Tried casting the cell to a String, however it only returns 2015 error. Any ideas on how to do this?