Sandwich existing formulas with "If iserror(formula),0,formula)"

O

ORLANDO VAZQUEZ

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
 
P

Pete_UK

If you are getting a #DIV/0! error from your VLOOKUP, then the error
is in the workbook where you are getting the data from, rather than in
the VLOOKUP formula itself. Consequently, you should look at the
"sending" workbooks and eradicate any errors that are in there.

Hope this helps.

Pete
 
M

Mike H

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
 
O

ORLANDO VAZQUEZ

This worked perfectly.

Thank you so much Mike.


Mike H said:
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 said:
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
Click to expand...
Click to expand...
 
O

ORLANDO VAZQUEZ

Of course, but Mike gave the answer right to the point.

Thanks Pete.


Pete_UK said:
If you are getting a #DIV/0! error from your VLOOKUP, then the error
is in the workbook where you are getting the data from, rather than in
the VLOOKUP formula itself. Consequently, you should look at the
"sending" workbooks and eradicate any errors that are in there.

Hope this helps.

Pete

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
Click to expand...

.
Click to expand...
 
M

Mike H

Glad i could help and thanks for the feedback

ORLANDO VAZQUEZ said:
This worked perfectly.

Thank you so much Mike.


Mike H said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

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(ISERROR(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


This worked perfectly.

Thank you so much Mike.


Mike H said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

Gord,

A good point, I never thought of that but for the formula I still prefer

F = Mid(c.Formula, 2)

instead of

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

Mike

Gord Dibben said:
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(ISERROR(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


This worked perfectly.

Thank you so much Mike.


Mike H said:
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

:

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
Click to expand...
Click to expand...

.
Click to expand...
 
O

ORLANDO VAZQUEZ

Good idea Gord.

Thanks.


Gord Dibben said:
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(ISERROR(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


This worked perfectly.

Thank you so much Mike.


Mike H said:
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

:

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
Click to expand...
Click to expand...

.
Click to expand...
 
G

Gord Dibben

Neater looking and shorter.

Gord

Gord,

A good point, I never thought of that but for the formula I still prefer

F = Mid(c.Formula, 2)

instead of

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

Mike

Gord Dibben said:
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(ISERROR(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


This worked perfectly.

Thank you so much Mike.


:

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

:

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
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Iserror with If statement and Vlookup 3
Ideas for simplifying cell formulas 4
Ideas for Simplifying Cell Formulas 2

Top