Thanks Nick,

I tried this but got some very strange characters appearing!!
I've managed to solve my problems from code I found on a different post.
Just in case anybody is interested, here it is....


Sub CreditComment()
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("H5").AddComment
Range("H5").Comment.Text Text:=v

End Sub




"NickHK" wrote:

Does this give the desired result ?

Dim i as long
Sheets("KPI values").Select
With rngAll.Cells
For i = i To .Count Step 2
v = v & .Item(i).Value & vbTab & .Item(i + 1).Value & Chr(10)
Next
End With

You need to add some error handling to deal with situations when .Count is
an odd number.

NickHK

"Meltad" wrote in message
...
Thanks again Nick, I get it now!
Do you know how I can display this data in 2 columns as opposed to 1
column?? This code makes my values paste as one long list in the comment
box,
would it be possible to split it ino 2 columns?

E.g from my range B11:C11, B12:C12

I'm getting
B11
C11
B12
C12

and I need
B11 C11
B12 C12

Sub CommentCredits()

Dim v As String
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngAll As Range
Dim Cell As Range

Set rng1 =

Range("C9,F9,B10,B11:C11,B12:C12,B13:C13,B14:C14,B 15:C15,F15,B16,B17:C17,B18
:C18,B19:C19,B20:C20,B21:C21,B22:C22")
Set rng2 =

Range("F22,B23,B24:C24,B25:C25,B26:C26,B27:C27,B28 :C28,B29:C29,B30:C30,B31:C
31,B32:C32,B33:C33,B34:C34,B35:C35")
Set rng3 =

Range("B36:C36,B37:C37,B38:C38,B39:C39,F39,B40,B41 :C41,B42:C42,F43,B43,B44:C
44,F44,B45,B46:C46,B47:C47,F47,B48,B49:C49,B50:C50 ")
Set rngAll = Union(rng1, rng2, rng3)

Sheets("KPI values").Select
For Each Cell In rngAll
v = v & Chr(10) & Cell.Value
Next
Range("H11").AddComment
Range("H11").Comment.Text Text:=v

End Sub

Thank you Nick :-)


"NickHK" wrote:

From my code you are looping
For Each cell in rngAll
so you are looking at the Cell.Value

NickHK