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

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


"Rich" wrote in message
...
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

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




In article ,
Rich wrote:

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

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.

"Rich" wrote:

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

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

--


Gary


"Rich" wrote in message
...
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.

"Rich" wrote:

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