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
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
Sub ZapFormulaValuesUserInput() | |
Dim MyRange As Range | |
Dim Cell As Range | |
Dim prv As Variant | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
With ActiveSheet | |
On Error GoTo handler | |
Set MyRange = Application.InputBox(Prompt:="Please Select a Range", _ | |
Title:="Choose Range to convert to values", Type:=8) | |
For Each Cell In MyRange.SpecialCells(xlCellTypeVisible) | |
If Not IsEmpty(Cell) = True Then | |
prv = Cell.Value | |
If Cell.HasFormula = True Then Cell.Value = prv | |
End If | |
Next Cell | |
handler: MsgBox ("Operation Cancelled or Completed") | |
End With | |
End Sub |
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
Sub ZapValuesNoUserInput() | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'paste special as values | |
For Each Cell In ActiveSheet.Range("AB2:AB" & ActiveSheet.UsedRange.Rows.Count) | |
On Error Resume Next | |
If Cell.Value <> "0" And Len(Cell) > 1 Then | |
Cell.Activate | |
ActiveCell.Copy | |
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats | |
End If | |
Next Cell | |
Application.CutCopyMode = False | |
End Sub |
Happy VBA coding!