• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get text from cell comments

will31

New Member
Hi,


I am trying to find a way to obtain the text from a comment in a cell. I need this to work similar to a lookup since the cell address will be dependant on the project number (to give the row) and the column header (to give the column).


I found a UDF on Ozgrid;


http://www.ozgrid.com/VBA/ExtractCommentText.htm


I can't figure out how to create the coordinates formula to give the column and row number and the code gives an argument error at the clean_ part;


Code:


Function GetCommentText(rCommentCell As Range)


Dim strGotIt As String


On Error Resume Next


strGotIt = WorksheetFunction.Clean _


(rCommentCell.Comment.Text)


GetCommentText = strGotIt


On Error GoTo 0


End Function


Any ideas?
 

Hui

Excel Ninja
Staff member
Will


The code should be

[pre]
Code:
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean (rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
[/pre]

To use it Put a Comment in Say E5


and use =GetCommentText(E5)


The clean _ part means the next line of code is a continuation of the current line


You have to watch when you copy code of the web a lot of the non Alpha Numeric characters don't copy correctly

especially _, ", ' and - characters

If you see Red in VBA look for those characters and retype them as they should be
 

will31

New Member
Thanks Hui that works great.


Do you know of a way to use formula's to obtain the cell reference based on a condition? The column will not change but the row will be dependant on the contents of column A:A.
 

Hui

Excel Ninja
Staff member
Have a look at either the Indirect or Offset functions.

Chandoo has done articles on both.
 

will31

New Member
I created a formula;


=GetCommentText((INDIRECT("'"&$B3&"'!$O$"&MATCH(MultiSearch!$C3,INDIRECT("'"&$B3&"'!$A$2:$A$1000"),0))))


this returns the correct coordinates and sheet but doesn't give the comment. I'm confused why it doesn't work
 

Hui

Excel Ninja
Staff member
Will

Try doing the GetCommentText manually and put the reference in manually

and also try =text((INDIRECT("'"&$B3&"'!$O$"&MATCH(MultiSearch!$C3,INDIRECT("'"&$B3&"'!$A$2:$A$1000"),0))),"Text")

the 2 references should be the same

Look for extra spaces that the formula may have added

or correct "" etc
 
Top