Replacing data using the cell value not the formula

Hi all,

I'm trying to replace data in a cell range using the cell's value instead of it using the formula.

I'm using the following code:-

ActiveSheet.Range("I:I").Select
Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

However, when I run the macro it errors at this point.

Basically, I'm trying to remove the dreaded #N/A and replace it with a 0. Is there an easier way of doing this??

Thanks!

Why not include it in your formula:
=If(ISNA(<YourFormula>),0,<YourFormula>)

NickHK

"Hunter1978" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all,
>
> I'm trying to replace data in a cell range using the cell's value
> instead of it using the formula.
>
> I'm using the following code:-
>
> ActiveSheet.Range("I:I").Select
> Selection.Replace What:="#N/A", Replacement:="0",
> LookAt:=xlPart, LookIn:=xlValues, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
> _
> ReplaceFormat:=False
>
> However, when I run the macro it errors at this point.
>
> Basically, I'm trying to remove the dreaded #N/A and replace it with a
> 0. Is there an easier way of doing this??
>
> Thanks!
>
>
> --
> Hunter1978
> ------------------------------------------------------------------------
> Hunter1978's Profile:
http://www.excelforum.com/member.php...o&userid=37261
> View this thread: http://www.excelforum.com/showthread...hreadid=569786
>

Hi NickHK,

Thanks for your answer.

It seemd to have done the trick!

Many thanks!