Macro to clear zeroes

[Howardc1001]

I have a workbook that is linked to another workbook

I have tried to write code to clear all zeroes on the activesheet, but cannot get it to work. Where the data is linked to another workbook and shows a zero is must be cleared , except where there is a zero is shown as a currency i.e. it has an R in front of the zero

Please Login or Register  to view this content.

I have attached the source workbook "Data analysis Dept 1.xlsx' as well as the destination workbook containing the macro "Data Analysis All Departments.xlsm"


Your assistance in resolving this is most appreciated

[JLGWhiz]

Your zeros are derived from formulas in each cell. If you try to use vba to clear the contents, you will wipe out your formulas. The way I would approach the issue is to change the formula to include an If function like"

Please Login or Register  to view this content.

any time you are dealing with values created by formula, you have to either use an IF funcion to control what the cell containing the formula shows, or establish a control for the source that the formula evealuates to produce the value shown in the cell with the formula. Trying to control values of cells with formulas by using vba is not recommended.

[Howardc1001]

Thanks for your input and valued solution using a formula

[Howardc1001]

Although the formula works perfectly, when I copy the formula to the right and down, the font changes and percentage changes to for eg 0.3448


How do I retain same format as source workbook ?

[Howardc1001]

Have resolved issue. after linking formula with Paste Special Link, I used Paste Special format

[JLGWhiz]

Glad you worked it out.
regards, JLG