JimS
01-26-2013, 08:57 AM
The code below works fine if there is only 1 syntax in the formula that its being applied to.
For example if the original formula is =GETPIVOTDATA(Field1,Field2,etc) the code below will convert it to =(IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc)),0,GETPIVOTDATA(Field1,Field2, etc)) perfectly.
I need the code to be able to handle when the original formula has more than one syntax, for example, =GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc).
The code below will convert this to =IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc) ),0,GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc)).
If either one of the GETPIVOTDATA results in an error the whole formula will result the a 0.
I need it to place the =IF(ISERROR on each of the individual GETPIVOTDATA formulas seperately.
Something like =IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc),0,GETPIVOTDATA(Field1,Field2,et c)),+IF(ISERROR(GETPIVOTDATA(Field3,Field4,etc),0,GETPIVOTDATA(Field3,Field 4,etc)).
This way if 1 of the GETPIVOTDATA produces an error the other part will still return its results.
Any ideas?
Thanks...
JimS
Sub WrapFormula()
Dim rnge As Range, cl As Range, strFormula As String
Set rnge = Selection
For Each cl In rnge.Cells
strFormula = cl.Formula
If InStr(1, strFormula, "ISERROR") = 0 Then
strFormula = "=IF(ISERROR(" & Right(strFormula, Len(strFormula) - 1) & "),0," & _
Right(strFormula, Len(strFormula) - 1) & ")"
cl.Formula = strFormula
End If
Next
End Sub
For example if the original formula is =GETPIVOTDATA(Field1,Field2,etc) the code below will convert it to =(IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc)),0,GETPIVOTDATA(Field1,Field2, etc)) perfectly.
I need the code to be able to handle when the original formula has more than one syntax, for example, =GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc).
The code below will convert this to =IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc) ),0,GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc)).
If either one of the GETPIVOTDATA results in an error the whole formula will result the a 0.
I need it to place the =IF(ISERROR on each of the individual GETPIVOTDATA formulas seperately.
Something like =IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc),0,GETPIVOTDATA(Field1,Field2,et c)),+IF(ISERROR(GETPIVOTDATA(Field3,Field4,etc),0,GETPIVOTDATA(Field3,Field 4,etc)).
This way if 1 of the GETPIVOTDATA produces an error the other part will still return its results.
Any ideas?
Thanks...
JimS
Sub WrapFormula()
Dim rnge As Range, cl As Range, strFormula As String
Set rnge = Selection
For Each cl In rnge.Cells
strFormula = cl.Formula
If InStr(1, strFormula, "ISERROR") = 0 Then
strFormula = "=IF(ISERROR(" & Right(strFormula, Len(strFormula) - 1) & "),0," & _
Right(strFormula, Len(strFormula) - 1) & ")"
cl.Formula = strFormula
End If
Next
End Sub