How to do a vlookup or index match that includes comments

[m5edward]

Hi Everyone,

I am currently trying to compile data from several worksheets into one summary worksheet.

My data contains many cells with comments, and those comments are extremely important and need to be brought over into my summary sheet.

I was able to find a macro that brought over notes, but not comments. The two names (notes and comments) get used interchangeably in forums. For the record, I want to vlookup comments with the purple indicator, not notes with the red indicator. See below.

Here is the macro that brought over my notes, but not comments. Can someone help me re-work this macro to bring over comments instead of notes? Please and thank you.

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

Thanks,

Mark