Excel 2007: SpecialCells(xlCellTypeComments)

[BrianWen]

I have a script that among other things finds every comments in a sheet and processes them with this code:

Set rngComment = Sheets("HelpSheet").Range("E5:E300").SpecialCells(xlCellTypeComments)

If rngComment Is Nothing Then
GoTo bla
End If

For Each rngTemp In rngComment
bla bla bla
Next

It works perfectly as long as there is a single comment or more. However, if there are no comments, the code just sort of stops at Set rngComment = Sheets("HelpSheet").Range("E5:E300").SpecialCells(xlCellTypeComments)

Meaning I can have a msgbox just before that line and one just after, and when there's no comments in the sheet, only the first one is shown. No errors, no nothing...That's a problem since there is a lot of code after that...

Any good ideas?

 

[combo]

SpecialCells raises error if no cell that satisfies condition, so:

On Error Resume Next
Set rngComment = Sheets("HelpSheet").Range("A1:f300").SpecialCells(xlCellTypeComments)
If Err.Number <> 0 Then
    Err.Clear
Else
    ' process rngComment
End If

combo

 

[BrianWen]

LOL, stupid me. How could I forget that.

I knew it of course, but as such I never use...Usually I just program my code not to fail

Thanks for the answer!