Find and Replace #DIV/0!

[se7098]

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?

 

[Gary''s Student]

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

 

[se7098]

ok...can i just say you are a GENIUS!!!! Thanks so much...SO MUCH TIME
SAVED!!!

 

[Gary''s Student]

Glad I could help. Actually, I can use this sort of thing myself.

 

[se7098]

how are you with using multiple criteria in a vlookup and sumif?

 

[Gary''s Student]

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.

 

[se7098]

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.

 

[se7098]

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.