This version will produce two columns in the comment. Although the alignment
is not perfect is it legible.

Sub meltad()
Dim v As String
Dim r As Range


Sheets("KPI values").Select
Range("B9:C50").Select
Flip = 1
For Each r In Selection
If r.Value = 0 Then
w = " "
Else
w = r.Value
End If

If Flip 0 Then
v = v & Chr(10) & w
Else
v = v & " " & w
End If
Flip = Flip * -1#
Next
Range("H11").AddComment
Range("H11").Comment.Text Text:=v
End Sub
--
Gary''s Student


"Meltad" wrote:

Ooh, aswell, I forgot to add...
how hard would it be to NOT include zeros (or their corresponding code in
the neighbouring cell)???

I've got data like this:

Credits Debits
210 DS 363 92
999 OE 0 732
190 TS 0 0

and need 2 seperate comments (one for credits one for debits)...
Putting in one column is lots better than not, but the list is quite long so
to exclude zeros would be brill!!

Thank you
"Gary''s Student" wrote:

I am glad you are having success!

to get a better-look comment, just replace

v = v & r.Value

with

v = v & Chr(10) & r.Value

this will produce a single column in the comment. Making two columns in the
comment is a little more tricky.
--
Gary''s Student


"Meltad" wrote:

Thanks GS!

Finally I've pasted something into a comment box!!

Now another thing... do you know if I'm able to copy formats cos what Ive
pasted is all jumbled etc. Almost need to be able to paste as a table?....

Is this pushing my luck!!

Thanks, Mel



"Gary''s Student" wrote:

How about:

Sub meltad()
Dim v As String
Dim r As Range

Sheets("KPI values").Select
Range("B9:C50").Select
For Each r In Selection
v = v & r.Value
Next
Range("H11").AddComment
Range("H11").Comment.Text Text:=v
End Sub


You can also concatenate CHR(10)'s into v to improve the appearance.
--
Gary's Student


"Meltad" wrote:

Hi,

I'm trying to copy a range in my worksheet then paste this to a single
comment in a neighbouring cell.

So far I've found this code for pasting from the clipboard to a comment
(courtesey of Bob Greenblatt)....

Sub InsertComment()
Dim oComm As DataObject
Set oComm = New DataObject
With ActiveCell
.AddComment
oComm.GetFromClipboard
.Comment.Text Text:=oComm.GetText(1)
End With
End Sub


however I get an error (user type not defined) - so I must have left
something out. Any ideas anyone??

I want to...

Range("H11").AddComment ""
Sheets("KPI values").Select
Range("B9:C50").Select
Selection.Copy

then paste this into the comment box.... ???

THANKS, Mel