PDA

View Full Version : Solved: VBA to convet to IF(ISERROR formula



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

Bob Phillips
01-26-2013, 12:27 PM
What is wrong with your suggestion?


=IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc),0,GETPIVOTDATA(Field1,Field2,et c)))
+IF(ISERROR(GETPIVOTDATA(Field3,Field4,etc),0,GETPIVOTDATA(Field3,Field4,et c)))

or if you have Excel 2007/2010/2013



=IFERROR(GETPIVOTDATA(Field1,Field2,etc),0)
+IFERROR(GETPIVOTDATA(Field3,Field4,etc),0)

JimS
01-26-2013, 01:25 PM
The code doesn't convert it to =IF(ISERROR(GETPIVOTDATA(Field1,Field2, etc),0,GETPIVOTDATA(Field1,Filed2,etc))+IF(ISERROR(GETPIVOTDATA(Field1,Fiel d2, etc),0,GETPIVOTDATA(Field1,Filed2,etc))

it does:

=IF(ISERROR(GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc) ),0,GETPIVOTDATA(Field1,Field2,etc)+GETPIVOTDATA(Field3,Field4,etc)).

Bob Phillips
01-26-2013, 05:18 PM
You have to process each element

Sub WrapFormula()
Dim rnge As Range, cl As Range, strFormula As String
Dim parts As Variant
Dim i As Long

Set rnge = Selection

For Each cl In rnge.Cells

strFormula = cl.Formula

If InStr(1, strFormula, "ISERROR") = 0 Then

parts = Split(Replace(strFormula, "=", ""), "+")
strFormula = ""
For i = LBound(parts) To UBound(parts)

strFormula = strFormula & "+IF(ISERROR(" & parts(i) & "),0," & parts(i) & ")"
Next i

cl.Formula = Replace(strFormula, "+", "=", 1, 1)
End If
Next
End Sub

JimS
01-27-2013, 09:23 AM
xld,

Perfect (as always).

Thanks so much...

JimS