Adding similar update to multiple different functions at once

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi,
I'm hoping there's a smarty out there that can assist me
in a question.
I would like to know if it's possible to update multiple
worksheet functions at the same time with the same
addition.

EG.
I have a template that includes a list of all possible
combinations of results and then in the results column
next to it the cells are all linked to a pivot table
containing all the data using GETPIVOT

The pivot table gets updated monthly and some results are
removed from the table if they didn't occur in that
month.

What I would like to do is update all my GETPIVOT
formula's with an =IF(ISERROR(GETPIVOT blah blah),0,
(GETPIVOT blah blah)) so I get 0's rather than #REF.

The template was created before my time and contains
close to 1300 linked cells - so I'm trying to find a way
to update all the cells. I've thought about a find and
replace but can't figure out how that would work. I've
also thought about a macro but I have no idea how to
write one that could do this.

Can anyone help me?
If you can, that's fantastic!
If you can't, thanks for reading anyway, I appreciate the
intention.

Cheers
CJ
 
One way:


Public Sub ReplaceGetPivotData()
Const sFIND As String = "GETPIVOTDATA"
Const sNEW As String = "IF(ISERR($$),0,$$)"
Dim rCell As Range
Dim rFormulas As Range
Dim nChar As Integer
Dim nPos As Integer
Dim sFormula As String
Dim sRepl As String
Dim bChanged As Boolean
On Error Resume Next
Set rFormulas = _
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulas Is Nothing Then
For Each rCell In rFormulas
With rCell
sFormula = .Formula
nPos = InStr(2, sFormula, sFIND)
Do While nPos > 0
nChar = InStr(nPos, sFormula, ")") - nPos + 1
sRepl = Mid(sFormula, nPos, nChar)
sFormula = Replace(sFormula, sRepl, _
Replace(sNEW, "$$", sRepl))
nPos = InStr(nPos + Len(sNEW) + _
2 * nChar - 4, sFormula, sFIND)
bChanged = True
Loop
If bChanged Then
.Formula = sFormula
bChanged = False
End If
End With
Next rCell
End If
End Sub

Note: if using XL97/98/01/v.X, replace the Replace() method with
Application.Substitute()

Note also that this will cause errors if the arguments within the
GETPIVOTDATA() parens have parentheses.
 
Back
Top