Oh dear!! r was range ( Dim r As Range) when I had all my range as one line
before I used union and rngAll.

So what should that be?? Just tried v = v & Chr(10) & rngAll.Value
and that didnt work.....




"NickHK" wrote:

What is r ?

NickHK

"Meltad" wrote in message
...
Hi Nick,
Thanks for that...

I expanded out my range into this long version cos I'm pasting the range
into a comment and this is the only way I can organise the format so it it
readable. If you know another way to display values as one (or even 2)
columns...

So... I still have the same error now but further down my code! I think
I'm
being thick!!

Sub InsertCommentCredits()

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) & r.Value
Next
Range("H11").AddComment
Range("H11").Comment.Text Text:=v

The error is pointing to
v = v & Chr(10) & r.Value

Any ideas???




"NickHK" wrote:

You don't need the .select when setting the range. Also you can combine
your
address somewhat:
set rng1=range("C9,B10,B11:C15,B16,B17:C22,F9,F15")
etc

And you don't need to .Select
Dim Cell As Range
For Each Cell In rngAll

NickHK

"Meltad" wrote in message
...
Hi,

I'm working on the following code to insert certain values into a
comment
but my range seems to be too big. I set up a Union (as found on this
discussion board) but get this run time error. Can anyone spot my
mistake??

Sub InsertCommentCredits()

Dim v As String
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngAll 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").Select
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").Select
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 ").Select
Set rngAll = Union(rng1, rng2, rng3)

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

End Sub

THANKS in advance!