How to search for "#N/A" in Excel VBA

[viking4020]

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

 

[Dave Peterson]

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.

 

[Stefi]

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:

 

[Ron Rosenfeld]

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]

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

 

[Ron Rosenfeld]

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