Excel: Clear cell in range if contains text


I'm working on an Excel 2013 macro that calculates values from another sheet within a workbook. If there are missing data in the first sheet, my calculations return "#Value!". This text screws up the next round of calculations so I was trying to figure out how to clear cells that contain "#Value!". I recorded the following by running a Find/Replace with "". It seemed to work until I ran it against a sheet that did not have any cells with "#Value!". Range(Cells(2, 3).Address, Cells(42,8).Address).Select Selection.Find(What:="#Value!", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.Replace What:="#Value!", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I then tried to include an If/Then statement: If Range(Cells(2, 3).Address, Cells(42,8).Address).Value = "#Value!" Then [I]'find/replace code above'[/I] End If ...but I get a Run Time Error 13 Type Mismatch. Please help.


This question generated 9 answers. To proceed to the answers, click here.

This thread is current as of October 24, 2014.


For more resources for Microsoft Excel