Excel VBA error - "Run-time error 1004: Unable to get the NoteText property of the Range class"

Question

Hi all,

the scenario is this

an existing excel package that includes several macros as part of a recurring reporting process

at a high level, the scripts process the data and prepare the reporting output, as well as retrieving related information from a previous worksheet and doing some comparisons

in the last reporting cycle this error was encountered and the team was unable to proceed past it and I have not had any success yet finding the root cause of the issue

the specific section of logic throwing the error is in the "lookup from previous worksheet" process, where it loops through the current reporting data and searches for a relevant row from the relevant tab of the previous workbook.

if found, it then attempts to retrieve the required information (first notetext, then amounts) from the relevant columns - see excerpt below

            Set searchRange = Range("A:A").Find(clientName, , xlValues, xlWhole)
            
            If Not searchRange Is Nothing Then
            
                searchRange.Activate
                                
                txt0 = searchRange.NoteText
                txt1 = searchRange.Offset(0, 1).NoteText
                txt2 = searchRange.Offset(0, 2).NoteText
                txt3 = searchRange.Offset(0, 3).NoteText
                txt4 = searchRange.Offset(0, 4).NoteText
				...

that section of code is where the error is being thrown

also tried referencing the ranges in the following ways in case that was (part of) the issue, but got the same error

'                txt0 = ActiveCell.NoteText
'                txt1 = ActiveCell.Offset(0, 1).NoteText
'                txt2 = ActiveCell.Offset(0, 2).NoteText
'                txt3 = ActiveCell.Offset(0, 3).NoteText
				...


'                txt0 = Cells(searchRange.Row, 1).NoteText
'                txt1 = Cells(searchRange.Row, 2).NoteText
'                txt2 = Cells(searchRange.Row, 3).NoteText
'                txt3 = Cells(searchRange.Row, 4).NoteText
				...

some additional context

the error is only happening when the lookup process gets to a specific tab on the previous workbook

i tried adding comments to other columns for the same record and other records for the same column, same error regardless

but comments were retrieved as normal from records on other tabs in the same workbook

ideally once I can understand what is causing the error, my goal would be to add some basic error handling before that step to produce a more meaningful msg for the end user

so i would welcome any suggestions anyone may have

thanks very much

Answer 1

The modern Excel supports two kinds of remarks: notes and comments, which can be found in Review tab of the Ribbon. It seems that NoteText cannot be used in case of comments.

To deal with both types, try this:

Dim n As Comment ' for notes
Dim c As CommentThreaded ' for comments

Set n = searchRange.Comment
If Not n Is Nothing Then 
    ' note
    txt0 = n.Text
Else
    Set c = searchRange.CommentThreaded
    If Not c Is Nothing Then 
        ' comment
        txt0 = c.Text
    End If
End If