How to convert infinity value to zero?

[Mohamedimran]

Hi,

I am new to this page. Sorry if something asked silly.

I was doing a report to my org with many sheets. In every sheets some cells indicate the infinity value like (#######) and it shows in percentage. It is not a manual formula. System gives the number like this then i have to change it to zero manually. I need a VBA script to avoid this and it saves time a lot. I uploading a sample file in that i have highlighted in yellow color infinity values which i want to convert to zero through macro.

 

[bosco_yip]

####### in cell means a column wide isn’t enough.

Try to double-click the right edge of the column header, or drag it to the width to suit you want.



Regards
Bosco

 

[Mohamedimran]

Hi Bosco, It is an infinity percentage it means no value. it should be considered as zero value. I was changing these numbers as zero manually in many tabs it take time to me to do this. please help me to fix this through macro.

if you check my attached file you can see the coloumn width is adequate already. if i stretch that it will give take more width

Thanks for you response.

 

[pecoflyer]

Which cells are you talking about?
(BTW XL does not now what infinite means)

 

[bosco_yip]

Here is your cell's value after double-click of the column header :



Regards
Bosco

 

[pecoflyer]

Same here. That's why I didn't understand. LibreOffice expands automatically