Working code for remove #Div,0 in column need a little change

[abjac]

HI I have below code, which remove remove all "DIV,0 errors and replace them with 0,00% in a choosen range.
I guess this will be easy for some, but have been fighting with it.
I want it to only take the cells in the range, which are not empty. As it is now it fill out all cells with 0,00%.
But would be nice and also more good looking if it only change the cells with value/error inside.

Please have a look

Sincerely
Abjac

Here is the code

Please Login or Register  to view this content.

[GeneralDisarray]

Are you sure you need a macro for this? You could just adjust the sheet with the formula i would think.

Can you clarify what you are looking to do with an example?

[abjac]

I use this macro. I just need the range to be changed so it only take the cells with value inside. I dont have formula in all cells.
So what it does is to change the cell when no data from "DIV,0 to this 0,00%, which look much more correct.
As the macro is now it fill up all cells with 0,00% which i dont want.
The code basically change the formula i have there. example =SUM(B26/B13) to this code =IF(ISERROR(SUM(B26/B13));;(SUM(B26/B13)))

And it do this inside the the range C25:C37 which is my upper example.



So that code i would like to have changed,

Hope it make sence
Abjac

[FDibbins]

You could just as easily use the =if(iserror()) function IN the formula that returns the #Div/0 error...

=if(iserror(your formula),0%,your formula)

[abjac]

HI FDibbins i think this is what the macro does. It change the formula. The only thing i would like to have changed is that is only put the formula in the cells which have a formula/value. Now it put the formula in all cells in the range.
So that is basically only what i need to have changed. The formula by it self works perfect

Sincerely
Abjac

[FDibbins]

If it worked "perfect" it wouldnt give you the error message

[abjac]

HI FDibbins. It works perfect,But have the #DIV;0, when there is no data.
And the code also works perfect. it just need to be changed to only take the cells with value in, inside the range.

Abjac

[abjac]

Here is a testsheet to try work with, maybe more easy to what i want. And yes i would like to create it with a macro.

Have a look

Sincerely
Abjac

[GeneralDisarray]

If you want the macro to not change a cell that is blank, then just add that logic to your code. I'm not really sure why you can't just have this already present in the sheet to begin with...

Please Login or Register  to view this content.

Also, I'm not sure if you wanted the 0,00% or did not want that... if you want a blank in place of the 0,00% then you can just add a "" in the formula instead of nothing. In other words, when the error is found, display a blank. Alter the code like this:

Please Login or Register  to view this content.

[abjac]

HI GeneralDisarray, Its really some great codes. Tested them and its working so good, special that you put both ways to do it.

Thanks so much.

Have a nice evening

Sincerely
Abjac