remove little green error triangle - how loop through to ignore er

[Guest]

I have hundreds of cells with formulas, and several of them have the little
green triangle because they reference another cell that is empty. I want to
ignore this/these errors. If I set up a range object in VBA, I can loop
through all the range areas and loop through all the respective cells. But
what object do I need to reference in order to set a cell property to ignore
the error?

--here is some pseudocode - does Excel have an xlError type ? I don't get
intellisense from my range object here.

Dim rng As range
Set rng = Sheet1.Range("A1:E5, A8:E13, A16:E21")
For i = 1 To rng.Areas.Count
For j = 1 To rng.Areas(i).Columns.Count
For k = 1 To rng.Areas(i).Rows.Count
rng.Areas(i)(k,j).Error = xlError.Ignore
,,,

Thanks,
Rich

 

[Rick Rothstein \(MVP - VB\)]

Is the only thing you want to do is remove the green error triangle when a
formula refers to a blank cell? Or is there more to your request. If all you
want to do is remove the green triangle, and nothing else with respect to
it, then click Tools/Options on Excel's menu bar, select the "Error
Checking" tab and uncheck the Rules item labeled "Formulas referring to
empty cells".

Rick

 

[JE McGimpsey]

One way:

Dim rFormulas As Range
Dim rCell As Range
On Error Resume Next
Set rFormulas = Sheet1.Range("A1:E5,A8:E13,A16:E21").SpecialCells( _
xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulas Is Nothing Then
For Each rCell In rFormulas
rCell.Errors.Item(xlEmptyCellReferences).Ignore = True
Next rCell
End If

 

[Guest]

Thank you all for your responses. I will be emailing the sheet in question
to other people who may not have checked off the Option for error checking.
So I need to do this in VBA. JE has a solution. Thanks.

question: If I run this loop for all cells, including cells that don't have
a green triangle, will this impair anything? I will guess not.

 

[Gary Keramidas]

this may work for you, too. just pick a blank cell, i chose M1.

it will only affect constants

Option Explicit
Sub test()
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
With ws
.Range("M1").Copy
.UsedRange.SpecialCells(xlConstants).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
End With
End Sub