Replacing value

tenspeed2

Board Regular
Joined
Jan 25, 2011
Messages
56
Hi guys I have tried using find and replace command to get rid of #Value!.
For some reason if I use the tilde ~ command the find and replace will only find two
of the many #Value! entries. Is there another way to find and replace the #Value! with a blank?
There are many #Value! cells to replace in the Excel ss.
Max
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
TenSpeed2,
Place this code in your sheet's VBE and run it once. It places an iferror statement in each of the cells.
Code:
Sub Add_IFERROR_Activesheet()Dim myCell As Range
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
End Sub

One caveat is that it replaces errors with a 0 (zero), not a blank. I've tried to mess with it to change the zero to a blank but I get an error. Maybe someone who knows more about VB can change it. Otherwise, if zeros vs. #Value! isn't a problem for you, this should do the trick.
 
Upvote 0
It's very simple. Highlight your data then press Ctrl + G. This brings up the Go To Box. Click to the Special Button. Tick the formulas circle which will ungrey the boxes below it. Untick everything apart from Errors which should have a tick on it. Then press OK. This has now highlighted all the cells with errors in them. You can either just press delete or you could replace the content of the cells (say with a zero) by pressing 0 then Ctrl + Enter
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top