Error Search

[Guest]

I need to programatically look for any #N/A across multiple worksheets in a
workbook. I have VBA code that "compiles" multpile workbooks each with
multiple worksheets into a single multi worksheet file. But before I copy
the information from one workbook to another I want to look at all of the
worksheets and make sure there is not #N/A on any of them. I did a lot of
reading on this and can not find what I need.

Any help would be appreciated. If I did not make myself clear sorry.

 

[Guest]

Hi Cyberwolf,

Hope this helps.

Sub Find_Error()
Dim wks As Worksheet 'Each worksheet in workbook
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String
Dim strToFind

strToFind = "#N/A"
For Each wks In Worksheets
Set rngToSearch = wks.Cells

Set rngFound = rngToSearch.Find(What:=strToFind, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
'Put your code here to handle the error
rngFound.Interior.ColorIndex = 6

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If

Next wks

End Sub

Regards,

OssieMac