Create VBA Formula to Add Comment to Cell

Hey everyone

I'm looking to move a lot of comments from a large worksheet around and would really like to avoid doing so manually and rather do it through formulas.

I am able to retrieve a cell's comment by using the VBA code as follow:

Function GetComments(pRng As Range) As String
If Not pRng.Comment Is Nothing Then
GetComments = pRng.Comment.Text
Dim CommentNow As String
CommentNow = pRng.Comment.Text
End If
End Function


I can then use the UDF =GetComments() to retrieve the comment from a cell and display it's text. I have the formula saving the text as the string CommentNow.

I'd like to be able to then check if CommentNow is blank and if not, put the string in the current cell.

I will be needing to use this in the form

=IF(GetComments(Index(,Match(),Match()))="","",CellToComment(GetComments(Index(,Match(),Match())))

I need to create the second UDF in this case called CellToComment to place the string CommentNow defined by the UDF GetComments if it is not blank.

I've been trying and troubleshooting for far too long now. I hope someone can help. It seems like it should be simple enough.

Additionally the cell will already have a value in it so I'd like to be able to use the formula =2816&IF(GetComments(Index(,Match(),Match()))="","",CellToComment(GetComments(Index(,Match(),Match())))

That may be asking too much but we'll see.

Cheers! And thanks guys

Can you elaborate on what you want the UDF CellToComment to do?