Changement text column

Option Explicit
Sub test()
Dim c As Range
For Each c In Range("G1:G" & Range("G65536").End(xlUp).Row)
    If UCase(c.Text) = "#REF" Then c = ""
Next c
End Sub

Sub TransErreurRef()
    Dim c As Range
    Dim plage As Range
     On Error Resume Next
     Set plage = ActiveSheet.Range("G:G").SpecialCells(xlCellTypeFormulas, xlErrors)
     If Err <> 0 Then GoTo FIN
     If Not plage Is Nothing Then
        For Each c In plage
         If c = CVErr(xlErrRef) Then c.ClearContents
        Next
     End If
FIN:
End Sub