Correct #REF using vba macro

[UHsoccer]

I have instances where a #REF! is displayed.

What is the VBA code to scan a sheet and replace all occurences with a specific value.
I tried the following, to no avail

Sheets("Emp_Availability").Select
 
Cells.Replace What:="#REF!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

Thanks

 

[jkpieterse]

Replace always replaces in Formulas, if the #REF error is the result of a formula, then it figures the replace fails.

 

[klarowe]

I'm assuming the #REF is showing up due to a formula?
Doubt this will work if that is the case, but I tested this with just actual cell values being "#REF":

Sub FindReplace()
        Sheets("Sheet1").Cells.Replace What:="#REF", Replacement:=""
End Sub

and it worked on my test sheet...

 

[Gary McMaster]

Try the sample below. I believe the Find & Replace commands use the same parameters as the last usage. Your last find may have been "LookIn:<wrong place>"

Gary

Public Sub Test()

Dim oCell As Range

Set oCell = Cells.Find(What:="#REF!", LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, MatchCase:=False)

If Not oCell Is Nothing Then oCell.Value = ""

End Sub

 

[jkpieterse]

I guess this works:

Sub ReplaceRefErrors()
    Dim oCell As Range
    For Each oCell In Worksheets("Emp_Availability").UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        If oCell.Value = CVErr(xlErrRef) Then
            oCell.ClearContents
        End If
    Next
End Sub

 

[John Davis]

I have instances where a #REF! is displayed.

What is the VBA code to scan a sheet and replace all occurences with a specific value.
I tried the following, to no avail

Sheets("Emp_Availability").Select
 
Cells.Replace What:="#REF!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

Thanks

Does changing to xlWhole help?

Sheets("Emp_Availability").Select
 
Cells.Replace What:="#REF!", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False

 

[jkpieterse]

A change to xlWHole will not help if the error is the result of a formula, the Replace command *ALWAYS* works on formulas or constants, NEVER on formula results.

 

[John Davis]

A change to xlWHole will not help if the error is the result of a formula, the Replace command *ALWAYS* works on formulas or constants, NEVER on formula results.

I realize that. But the OP never clearified whether it was the result of a formula. Prior code to what was posted may have made the cells values. Therefore xlWhole might be what is needed.

 

[UHsoccer]

Apologies for not clarifying sooner, the #REF! comes from a formula where the cell reference is invalid.

A sample formula is : =TimeSheet!B9

But the content of B9 maybe inconsistent with the target cell.
I have since updated my macro to verify the FROM cell content.

However, I a still interested in the solution to the original question, since there are a number of other errors can surface, such as #DIV, #NULL etc.

It seems of general help, to be able to remove unexpected error content

 

[klarowe]

Does jkpieterse's code above work? At a quick glance it seems like it would take all formula errors into account... but I could be wrong...