Extract Cell Comments/Notes to data

[MSpaven75]

I have a spreadsheet with Yes/No's a field values and all the meaningful data
as cell comments, I know there is a way to get these notes as cell contents
but can't remember how.

Ideas anyone?

 

[Gary''s Student]

Lots of different ways.

Say we have comments in cells and want to put the data in the adjacent cell
to the right.

So if B9 had a comment, we want that text in C9.

Try this tiny macro:

Sub DisplayComments()
Dim c As Comment
If ActiveSheet.Comments.Count = 0 Then Exit Sub
For Each c In ActiveSheet.Comments
Range(c.Parent.Address).Offset(0, 1).Value = c.Text
Next
End Sub

 

[MSpaven75]

How do I use it though, I can't remember how to edit MACRO's/VBA (been ages!!!)

 

[Max]

I use this UDF (from David M, IIRC)

Function MyComment(rng As Range)
Application.Volatile
Dim str As String
str = Trim(rng.Comment.Text)
'// If you want to remove Chr(10) character from string, then
str = Application.Substitute(str, vbLf, " ")
MyComment = str
End Function


In Excel,
use it in say, B1: =mycomment(A1)
to return A1's "Comment" contents
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[MSpaven75]

Thanks, but, again, I have forgotten hopw to create UDF's etc. (it was a UDF
I have used before)

Can someone talk me through as if I were daft? (which I am not, honest)

 

[Jarek Kujawa]

Toools->Macros->select a macro, then click on Edit button to the right

 

[Max]

No problem, here's the steps to install it:

Press Alt+F11 to go to VBE
Click Insert>Module
Copy n paste the UDF (or Sub) into the code window (whitespace on the right)
Press Alt+Q to get back to Excel

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[MSpaven75]

Thanks to those who helped, this is now working.

I'll make a two pint donation to the "I like beer, buy me beer" fund over
the weekend.