put a comment in row 1 of selected column

[auntie bella]

I found this code on another post which almost does what I want....

========================================
Sub test()
Dim c As Range, d As String
For Each c In Selection
If c <> "" Then d = d & c & Chr(10)
Next
d = Left(d, Len(d) - 1)
Range("A42").Comment.Delete
Range("A42").AddComment d
End Sub
========================================
However I want it to write the comment in row 1 of whichever column I happen to be in - without me having to keep changing the "A42" to whatever.
SO if I select
D4:D10 then the comment is in D1 or
P12:P36 then the comment is in P1 etc.

Can anyone advise?

thanks

 

[GreenyMcDuff]

Try this:

Sub test()
Dim c As Range, d As String
For Each c In Selection
If c <> "" Then d = d & c & Chr(10)
Next
Set Col = ActiveCell
    Cells(1, Col.Column).Comment Delete
    Cells(1, Col.Column).AddComment d
End Sub

Not tested, let me know what happens

 

[VoG]

Try

Sub testcmt()
Dim c As Range, d As String
For Each c In Selection
    If c <> "" Then d = d & c & Chr(10)
Next c
d = Left(d, Len(d) - 1)
With Cells(1, Selection.Column)
    On Error Resume Next
    .Comment.Delete
    On Error GoTo 0
    .AddComment d
End With
End Sub

 

[auntie bella]

Many thanks guys.....both solutions worked....

(GreenyMcDuff: I had to put a "." between Comment Delete to get it to work )

As I am still a beginner at this so can you explain (in detail) exactly what this line does (i understand the rest of the code)

d = Left(d, Len(d) - 1)


i know that the "d" contains my text from the selected cells but what is the signifcance of LEFT and LEN(d) and why -1?

thanks again.

 

[VoG]

The code builds up the comment text (d) adding a line feed (Chr(10)) at the end of each item. The code that you posted removes the last unnecessary line feed.

 

[auntie bella]

sorry but i still need a bit more information to understand fully.

if my selected cells contain "joe" and Ben"

when I hover over the Len(d) I get 7 ...

IS this made up of "joe" = 3 , line feed (1), "Ben" = 3 and line feed = 1 - so a total of 8 and the -1 has already been removed?


also what is the Left for ?

Sorry to go on about it but I want to learn.

 

[VoG]

You are correct in how the string is built up.

Len(d) gives the length of the string

Left(String, Number)

Returns the specified number of characters from the left of the string.

So

d = Left(d, Len(d) - 1)

returns d minus the rightmost character.

 

[auntie bella]

Many many thanks for your patience.