Finding Errors using the .Find in VBA

storm925

Board Regular
Joined
Jan 20, 2010
Messages
226
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...

Code:
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,881
Office Version
2013
Platform
Windows
Maybe this....highlights the error cells in red

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
Depending on your data
Code:
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

Board Regular
Joined
Jan 20, 2010
Messages
226
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,258
Messages
5,467,621
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top