Finding Errors using the .Find in VBA

[storm925]

Hi All,

Just need a little help refining my macro. I'm trying to find errors in a column using part of the following script...

Dim rngFound As Range

Set rngFound = Columns("N").Find(What:=IsError, After:=Range("N4"), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

MsgBox rngFound.Address

I'm trying to find any kind of error, e.g. #VALUE , #N/A, etc. I know that my problem is using "What:=IsError", just not sure what to replace it with?

Best,
Ben

 

[footoo]

Try using SpecialCells instead of Find.

 

[Michael M]

Maybe this....highlights the error cells in red

Sub MM1()
    ActiveSheet.Range("N:N").SpecialCells(-4123, 16).Interior.ColorIndex = 3
End Sub

 

[Fluff]

Depending on your data

Sub storm925()
   On Error Resume Next
   Range("N:N").SpecialCells(xlFormulas, xlErrors).Interior.Color = vbRed
   Range("N:N").SpecialCells(xlConstants, xlErrors).Interior.Color = vbBlue
   On Error GoTo 0
End Sub

This will highlight all errors.

 

[storm925]

Thanks for the tips, I didn't think about using .SpecialCells() I've been using .Find() for such a long time, kind of forgot about this one. Have a great day! --Ben

 

[Fluff]

Glad we could help & thanks for the feedback