div 0

[mp3er3000]

i have a macro that pull up data from Oracle server. After i pulled onto Excel I have functions that do calculations based on that data. The problem is sometims it displays as div 0 is there any way i can write a vba code that search through every cell and if it's div 0 then change it to 0. As of right i know how to search through every cell but it only give me the function instead of div 0. thanks

 

[KenWright]

Fix the functions such that if the denominator is 0 then the function returns 0, eg:-

Assuming you had =A1/B1 this will return #DIV/0 if B1 = 0, but

=IF(B1=0,0,A1/B1)

will return 0 if B1 = 0

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mp3er3000]

Thanks for the reply but my excel file is very big and hundreds of formulars. it would be a time consuming to edit each function. I was wondering if there is a vba macro or codes to search through each cell and change instead of using funtions in excel like you suggested. Thanks for the help!

 

[mp3er3000]

anyone got of a solution?

 

[KenWright]

Select the whole sheet using CTRL+A.

Do Edit / Go To / Special / Tick Formulas and then untick everything except 'Errors'.

Type 0 and use CTRL+ENTER to enter it.

This will change all Errors to 0

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

You realise of course though that this blows away your formulas and possibly screws it up for next time?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[byundt]

As an alternative, consider using Conditional Formatting to change the font color to white for any cell containing an error value. To do this:
1) Select all the cells in your worksheet
2) Open the Format...Conditional Formatting menu item
3) Choose "Formula Is" in the left dropdown
4) Enter the following formula:
=ISERROR(A1)
This formula assumes cell A1 is the top left cell in the selection
5) Click the "Format" button, then go to the "Font" tab and choose the white font color. This assumes that you are using the default white background color.
6) Click "OK" twice

Brad