I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.

Record a macro when you select a range (or all the cells)
edit|goto|Special
Errors

You'll be able to record a macro when you search for constants with errors and
formulas with errors. So you'll end up with two macros.

viking4020 wrote:

I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.

--

Dave Peterson

Use these lines in a loop stepping through the cells to be checked:
If WorksheetFunction.IsError(ActiveCell) Then
' your code
End If
Regards,
Stefi


€žviking4020€� ezt Ã*rta:

I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.

On Wed, 25 Jun 2008 04:57:01 -0700, viking4020
wrote:

I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.

Not very specific requirements, but the following should get you started:

Option Explicit
Sub foo()
Dim rStart As Range, rEnd As Range, c As Range
Set rStart = [A1]
Set rEnd = rStart.SpecialCells(xlCellTypeLastCell)

For Each c In Range(rStart, rEnd)
If IsError(c) Then
Debug.Print c.Address, c.Text
End If
Next c

End Sub
--ron

On Wed, 25 Jun 2008 08:41:08 -0400, Ron Rosenfeld
wrote:

On Wed, 25 Jun 2008 04:57:01 -0700, viking4020
wrote:

I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.

Not very specific requirements, but the following should get you started:

Option Explicit
Sub foo()
Dim rStart As Range, rEnd As Range, c As Range
Set rStart = [A1]
Set rEnd = rStart.SpecialCells(xlCellTypeLastCell)

For Each c In Range(rStart, rEnd)
If IsError(c) Then
Debug.Print c.Address, c.Text
End If
Next c

End Sub
--ron

Gary's student reminded me that UsedRange was the property I really wanted:

======================
Option Explicit
Sub foo()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If IsError(c) Then
Debug.Print c.Address, c.Text
End If
Next c
End Sub
====================
--ron

This will find and Select all occurances of #N/A:

Sub findena()
Set ena = Nothing
For Each r In ActiveSheet.UsedRange
If r.Text = "#N/A" Then
If ena Is Nothing Then
Set ena = r
Else
Set ena = Union(ena, r)
End If
End If
Next
ena.Select
End Sub

--
Gary''s Student - gsnu200793


"viking4020" wrote:

I wish to write vba code that finds occurrences of #N/A, #NAME, etc in Excel
spreadsheets.