Replacement Problem (Ctrl+H)

[keithfender]

I want to replace all the #VALUE!'s in a couple columns, but when i try Ctrl+H, I can onlny search in Formulas, and not in Values. Has this ever happened to anyone else? How can I get it so i can search in the cell value again so i can replace stuff?

[JBeaucaire]

Replace them with what? Are you trying to edit the formulas within the cells or just blast out the contents and replace them completely with something else?

For instance, this little stock macro will clear all the cells in columns J:O that have errors:

Sub RemoveErr()
'Clears all cells in range with error values
Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Columns("J:O").SpecialCells(xlCellTypeFormulas, 16).ClearContents
    Next ws
End Sub

That's designed to cycle through all the worksheets, but it can be changed to run on a single sheet, too.

[keithfender]

I already filled a column with the appropriate formula. I want to replace the #VALUE! with blank cells.

[JBeaucaire]

Well, here's a version that will clear all the cells on the current sheet with errors:

Sub RemoveErr()
'Clears all cells on activesheet with errors
    Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub

Here's a version that will only clear the error cells in the range already highlighted when you run the macro:

Sub RemoveErr()
'Clears all cells in the selected range with errors
    Selection.SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub

[shg]

You can do Edit > Go To, Special, Formula, untick everything except Errors, OK.

Then either hit Delete to clear the formulas, or enter a new formula or value in the formula bar, press and hold the Ctrl key, and then press Enter.

[JBeaucaire]

You can do Edit > Go To, Special, Formula, untick everything except Errors, OK.

Yep... that's the manual version of the macro I've suggested...at this basic level it really is almost as easy to just do it manually as it is to activate a macro. I would probably just do it this way.

[mubashir aziz]

I think you should not concentrate to delete the Err values except to make some change in your formula like if error then " " <blank cells> etc..... this is standard practice but i u don't have enough time to think on formula then while printing you can hide these values Just by File > Page Setup > Sheet > Here select Cell Error as <blank> so error cells will not be printed.

Else you can adopt any method to solve the problem.