Sandwich existing formulas with "If iserror(formula),0,formul
Just for info.
Mike's macro does not check to see if ISERROR already exists in the
formula(s)
It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.
=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))
I prefer to check as I go through the selected range.
Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub
Gord Dibben MS Excel MVP
On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:
This worked perfectly.
Thank you so much Mike.
"Mike H" wrote:
Hi,
Try this macro. Select the formula you want to change and run it. Try it in
a test environment first
Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub
Mike
"ORLANDO VAZQUEZ" wrote:
Hi,
I have several hundred different formulas on one spreadsheet like this one.
=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)
The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.
Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?
I can manually change them but that would take a day at least.
Please let me know.
Thank you,
Orlando
|