| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace #DIV/0!
I have inherited a massisve workbook to maintain and when the original
formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and Replace #DIV/0!
Try this small macro:
Sub FixUm() Dim er As String, equ As String, s As String er = "#DIV/0!" For Each r In ActiveSheet.UsedRange If r.Text = er Then equ = Right(r.Formula, Len(r.Formula) - 1) equ = "(" & equ & ")" s = "=IF(ISERROR(" & equ & ")" & ",""-""," & equ & ")" r.Formula = s End If Next End Sub -- Gary''s Student - gsnu200841 "se7098" wrote: I have inherited a massisve workbook to maintain and when the original formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and Replace #DIV/0!
ok...can i just say you are a GENIUS!!!! Thanks so much...SO MUCH TIME
SAVED!!! "Gary''s Student" wrote: Try this small macro: Sub FixUm() Dim er As String, equ As String, s As String er = "#DIV/0!" For Each r In ActiveSheet.UsedRange If r.Text = er Then equ = Right(r.Formula, Len(r.Formula) - 1) equ = "(" & equ & ")" s = "=IF(ISERROR(" & equ & ")" & ",""-""," & equ & ")" r.Formula = s End If Next End Sub -- Gary''s Student - gsnu200841 "se7098" wrote: I have inherited a massisve workbook to maintain and when the original formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and Replace #DIV/0!
Glad I could help. Actually, I can use this sort of thing myself.
-- Gary''s Student - gsnu200841 "se7098" wrote: ok...can i just say you are a GENIUS!!!! Thanks so much...SO MUCH TIME SAVED!!! "Gary''s Student" wrote: Try this small macro: Sub FixUm() Dim er As String, equ As String, s As String er = "#DIV/0!" For Each r In ActiveSheet.UsedRange If r.Text = er Then equ = Right(r.Formula, Len(r.Formula) - 1) equ = "(" & equ & ")" s = "=IF(ISERROR(" & equ & ")" & ",""-""," & equ & ")" r.Formula = s End If Next End Sub -- Gary''s Student - gsnu200841 "se7098" wrote: I have inherited a massisve workbook to maintain and when the original formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and Replace #DIV/0!
how are you with using multiple criteria in a vlookup and sumif?
"Gary''s Student" wrote: Glad I could help. Actually, I can use this sort of thing myself. -- Gary''s Student - gsnu200841 "se7098" wrote: ok...can i just say you are a GENIUS!!!! Thanks so much...SO MUCH TIME SAVED!!! "Gary''s Student" wrote: Try this small macro: Sub FixUm() Dim er As String, equ As String, s As String er = "#DIV/0!" For Each r In ActiveSheet.UsedRange If r.Text = er Then equ = Right(r.Formula, Len(r.Formula) - 1) equ = "(" & equ & ")" s = "=IF(ISERROR(" & equ & ")" & ",""-""," & equ & ")" r.Formula = s End If Next End Sub -- Gary''s Student - gsnu200841 "se7098" wrote: I have inherited a massisve workbook to maintain and when the original formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and Replace #DIV/0!
Pretty good.
With multiple criteria, always consider using SUMPRODUCT() rather than SUMIF(). This is an EXTREMELY powerful function and worth the effort to learn it. Update the post with your specifics. -- Gary''s Student - gsnu200841 "se7098" wrote: how are you with using multiple criteria in a vlookup and sumif? "Gary''s Student" wrote: Glad I could help. Actually, I can use this sort of thing myself. -- Gary''s Student - gsnu200841 "se7098" wrote: ok...can i just say you are a GENIUS!!!! Thanks so much...SO MUCH TIME SAVED!!! "Gary''s Student" wrote: Try this small macro: Sub FixUm() Dim er As String, equ As String, s As String er = "#DIV/0!" For Each r In ActiveSheet.UsedRange If r.Text = er Then equ = Right(r.Formula, Len(r.Formula) - 1) equ = "(" & equ & ")" s = "=IF(ISERROR(" & equ & ")" & ",""-""," & equ & ")" r.Formula = s End If Next End Sub -- Gary''s Student - gsnu200841 "se7098" wrote: I have inherited a massisve workbook to maintain and when the original formulas were entered they were not set up to display a dash instead of the #div/0! error...is there a way to search the entire workbook and auto replace the formula to surround it with the =IF(ISERROR(old_formula),"",old_formula) formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
| Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
| where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
| find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
| find replace cursor default to find box | Excel Discussion (Misc queries) | |||
Hybrid Mode
