Edit Excel Formula using VBA

[crack100]

Hi ,


I have an excel workbook which has formulas spread from Column B to Column EX. Now I want to edit all the formula to the new formula as "=iferror(<original formula>,"") " . The formula across all the columns are different. Is there a way In Excel that I can add the extra iferror formula. If not I have written a macro which would edit formula across only one column. How can I edit the macro to update it across column B to Column EX.


Sub update_formula()


Dim i As Integer

' One column


For i = 3 To 20

With Range("B" & i)

.Formula = Right(.Formula, Len(.Formula) - 1)

.Formula = "=IFERROR((" & .Formula & "),"""")"


End With

Next i


End Sub

 

[shrivallabha]

You can try something like this:

[pre]

Public Sub AddIfError()
Dim r As Range
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
r.Formula = "=IFERROR(" & Mid(r.Formula, 2, 1024) & ","""")"
Next
End Sub

[/pre]

 

[crack100]

Thank you . I will try this