Replace #¡VALOR!

[CoolDann]

Hello, I have some formulas in a cells, but some these due to formula, receive this "#¡VALOR!", and I want to replace this "value" with "", I s this possible?, if so, how can I Do it. I made this macro, but can raplace any string, but with "#¡VALOR!" DON'T WORKS. the code is this:

' Replace

Cells.Select
Selection.Replace What:="#¡VALOR!", Replacement:="", LookAt:=x1Part, _
_
SearchOrder:=x1ByRaws, MathCase:=False, SearchFormat:=False,_
ReplaceFormat:=False

Thanks in advance for the help

 

[chicagodiceman]

Searched the help file and can't find "#¡VALOR!" Is this the English version of Excel and if so, which version???

 

[cgcamal]

Hello CoolDann

Maybe trying including in your worksheet formulas IFERROR function like this.

=IFERROR(YOUR_FORMULA,"")

If you receive #¡VALOR! maybe your are using Spanish version of Excel then the formula should be,

=SIERROR(YOUR_FORMULA,"")

or portuguese version

=SE.ERRO(YOUR_FORMULA,"")

Hope this helps.


Regards

 

[VoG]

You have x1 instead of xl twice. It should be

Cells.Select
Selection.Replace What:="#¡VALOR!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRaws, MathCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Peter_SSs]

I would also suggest addressing the problem by adjusting the formula, but if you are using Excel 2003 or earlier you would need a different approach to the one suggested by cgcamal because the IFERROR function only appeared in Excel 2007. You would need (possibly translated)

=IF(ISERROR(your_existing_formula),"",your_existing_formula)

 

[Peter_SSs]

Note also that Find/Replace will not replace the results of a formula.
And if you were trying to Find/Replace in the formulas themselves, or in non-formula cells, you would also need to look at
xlByRows rather than xlByRaws