Get text from cell comments

[will31]

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]

Will


The code should be

[pre]

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]

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]

Have a look at either the Indirect or Offset functions.

Chandoo has done articles on both.

 

[will31]

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]

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