Run time error '424' object required

[Meltad]

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,B15: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:C31,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:C44,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!

 

[NickHK]

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]

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,B15: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:C31,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:C44,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]

What is r ?

NickHK

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,B15: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???

 

[Meltad]

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]

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

NickHK

 

[Meltad]

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,B15: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:C31,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:C44,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]

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]

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