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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace #DIV/0!
I have a spreadsheet that i need to populate from my source data.
It's kind of hard to explain but i will do my best without you having an actual example. it is a very large spreadsheet broken down by region/city/vp etc. each vp is assigned a region...so i need to be able to pull the values for individual vp's city etc. I need a formula that will go into the source file and locate a specified city...then look to the right of the city and find a specified job code number then look to the right and up 1 row and find a specified folder name and return the value below that folder name. the sum if or sumproduct would come in when i have a need to add the sums of multiple folders (i.e., although i only have the heading for interview i will need the value of the interview folder added to the selected folder - we did this in order to keep the headings to a minimum.) Example of data sheet to populate: inbox interview hire city: atlanta job code: 1234 example of source data: city job code inbox intv sel hire aberdeen albany atlanta Let me know if you need to see an actual example & i can forward you a snippet of the source data and the worksheet i need populated. Thanks so much - i am still learning and what i am learning is that i have a lot to learn. :) "Gary''s Student" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace #DIV/0!
I have a spreadsheet that i need to populate from my source data.
It's kind of hard to explain but i will do my best without you having an actual example. it is a very large spreadsheet broken down by region/city/vp etc. each vp is assigned a region...so i need to be able to pull the values for individual vp's city etc. I need a formula that will go into the source file and locate a specified city...then look to the right of the city and find a specified job code number then look to the right and up 1 row and find a specified folder name and return the value below that folder name. the sum if or sumproduct would come in when i have a need to add the sums of multiple folders (i.e., although i only have the heading for interview i will need the value of the interview folder added to the selected folder - we did this in order to keep the headings to a minimum.) Example of data sheet to populate: inbox interview hire city: atlanta job code: 1234 example of source data: city job code inbox intv sel hire aberdeen albany atlanta Let me know if you need to see an actual example & i can forward you a snippet of the source data and the worksheet i need populated. Thanks so much - i am still learning and what i am learning is that i have a lot to learn. :) "Gary''s Student" wrote: 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) |