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
'find/replace code above'
End If
...but I get a Run Time Error 13 Type Mismatch. Please help.
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
'find/replace code above'
End If
...but I get a Run Time Error 13 Type Mismatch. Please help.