How Can I Quickly Zap the Formulas in a cell range?

When I was working as a Reporting Analyst, I was responsible for generating the monthly EMEA Delivery Management scorecards, whose results would get discussed in a day-long meetings between all senior management members

One of the major issues with these scorecards when I inherited them was that there was no consistent way of regularly feeding all the relevant data, consequently the error rate was very high.

To address the issue, I developed the so-called consolidated feed where all the necessary indicators would be fed through getpivotdata() excel formulas at the time of their generation. That feed would be subsequently used as a sole input for the scorecards.

This, however, meant that whenever there was a change in fiscal quarter, I would have to make sure that I’ve got my QTD numbers copied and pasted as values to be available as a Q1, Q2, Q3 or Q4 references

Since the different scorecard indicators would get generated at different times during the month, I would only need to zap the QTD formulas of the indicators that were already complete.

Copying and pasting as values for the different ranges could be a very tedious exercise, so I came up with two approaches of tackling the task:

Approach 1: select formulas to scrap based on user input

Whenever run, the code will basically ask you to select the range of cells whose formulas you would like to have scrapped.

Then, it will check whether each cell part of the specified range contains a formula or not. If a formula is present, it will just replace the formula with its previously generated value.

Repeating this over and over again for multiple values in a single column could, of course, also get quite frustrating. An alternative approach then would be to:

Approach 2: scrap the formulas in all cells that are not empty in a given column

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.