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
> > > > >