copy comments from one cell into adjacent cell 1

[salmonman]

I am trying to copy the comments text from one cell into an adjacent cell. Is there an excel formula for this?
Ex..
Cell B3 (Value = 4, red triangle comment says "This means 4 apples"
I want cell C3 to have text in it saying "This means 4 apples".
Any help is appreciated. Thanks.

 

[Zathras]

I don't know of one, but if you can cope with VBA, you can write your own like this:

Option Explicit
Function GetComment(ReferenceCell) As String
  GetComment = Mid(ReferenceCell.Comment.Text, _
      Len(ReferenceCell.Comment.Author) + 3, 999)
End Function

Unfortunately, it won't update automatically when the comment in the referenced cell is changed. But if I understand your request, that shouldn't be a problem.

 

[salmonman]

Hey there - thanks for the reply.
Still having troubles though - I'm quite new at Visual basic. What I tried was copying the code into my personal.xls - and then inserting the function in excel by clicking on the function button - user defined - personal.xls-this workbook - Get comment.
Any suggestions?

 

[Zathras]

So when you clicked on the function button and selected User Defined, did you see "GetComment"? Then what happens? Where exactly did you copy the code?

BTW, the function as written is "quick and dirty" -- if there is no comment, it returns #VALUE. This is a more robust version:

Option Explicit
Function GetComment(ReferenceCell) As String
Dim cComment As Comment
  Set cComment = ReferenceCell.Comment
  If cComment Is Nothing Then
    GetComment = ""
  Else
    GetComment = Mid(cComment.Text, _
        Len(cComment.Author) + 3, 999)
  End If
  Set cComment = Nothing
End Function

 

[salmonman]

Yes - the 'getcomment' is in the user defined (well, it actually says Personal.xls!Thisworkbook.GetComment
Once selected... this baby comes up in the input field..
=PERSONAL.XLS!ThisWorkbook.GetComment()
As well as a prompt box that says.
"Choose the Help button for help on this funciton and it's arguments.
This function takes no arguments".
I tried putting my cell I want to get the comment from (ex. C9) in the equals line... but it doesn't seem to work.
=PERSONAL.XLS!ThisWorkbook.GetComment(C9)
It says the formula I have entered contains an error. Thanks for your help.

 

[Zathras]

OK. You pasted the code into "ThisWorkbook" code page. What you need to do is paste the code into a Module. On the VBA editor, click Insert/Module. That will open a new page. Past the code there, and delete the lines from "ThisWorkbook."

"ThisWorkbook" and "Sheet1" code pages allow you to handle events that can happen at the workbook and sheet levels respectively.

 

[shankbanta]

Being a non-VBA person (took the programming class several years ago and never used) how do I acomplish this in Excel?

 

[Zathras]

From Excel, key Alt-F11 to open the VBA editor.

In the VBA editor, select Insert/Module from the menu.

In the code page that was inserted, paste the code from my posting (everything in

blue

).

Close the VBA editor.

From Excel, assuming the cell with a comment is in A1, enter the formula in B1 (or wherever):

B1: =GetComment(A1)

Copy down as far as needed.
If you use the formula button on the toolbar, you will find the function under User Defined Functions.