Extract Cell Comments/Notes to data

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?

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

--
Gary''s Student - gsnu200819


"MSpaven75" wrote:

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?

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200819


"MSpaven75" wrote:

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?

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" wrote:
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?

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)

"Max" wrote:

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" wrote:
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?

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


On 11 Gru, 15:34, MSpaven75
wrote:
How do I use it though, I can't remember how to edit MACRO's/VBA (been ages!!!)



"Gary''s Student" wrote:
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

--
Gary''s Student - gsnu200819

"MSpaven75" wrote:

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?- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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

Press Alt+F11 to go to VBE
Click InsertModule
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" wrote:
Thanks, but, again, I have forgotten how 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)

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.