Altering formula for errors

[pctuk]

Hi

I'm trying to create a macro to alter a range of cells so that they are encased in an =IF(ISERROR(x),0,x) structure (where x is the formula that was previously in the cell). My attempt so far is:-

Please Login or Register  to view this content.

This isn't working so far - any tips on how I can change this? I'm also not sure that this would work if I selected a range of cells - could you help me add this please?

Thanks
Paul

[DonkeyOte]

Welcome to the Board.

There will be a few approaches, one (based on updating all formulae within selection at run time) might be:

Please Login or Register  to view this content.

your earlier code would work were you to correct the parentheses in the final formula, ie:

=IF(ISERROR(formula),0,formula)

rather than

=IF(ISERROR(formula,0,formula))

[pctuk]

Brilliant - thanks for that.

I just ran your code with one cell selected and my entire worksheet has had all of its formulas changed! Is there a way of tweaking the code so that it only runs on the selected cell(s)?

Thanks again - really appreciate the help

Paul

[DonkeyOte]

Alternative (avoiding SpecialCells)

Please Login or Register  to view this content.

[pctuk]

Perfect - works like a charm.

My next task is to create a macro that does the inverse and strips out the If(is(error()) code. My first idea was to use a FIND function to locate the sequence of

Please Login or Register  to view this content.

characters half way through the function, and then use some of the text manipulation functions (RIGHT?) to trim the rest of the formula off. However, I worry that this might catch a formula that happens to have this string of characters in it.

Is there a more elegant way of doing this?

Thanks again - you've been really helpful.

Paul

[DonkeyOte]

Perhaps along the lines of:

Please Login or Register  to view this content.

[pctuk]

Wonderful! Elegant and efficient, it most certainly is. Thanks a bunch