ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Run time error '424' object required (https://www.excelbanter.com/excel-programming/369717-run-time-error-424-object-required.html)

Meltad
Run time error '424' object required
 
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:C31,B32:C32,B33:C33,B34:C 34,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:C 47,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
Run time error '424' object required
 
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!

Meltad
Run time error '424' object required
 
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:C31,B32:C32,B33:C33,B34:C 34,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:C 47,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!

NickHK
Run time error '424' object required
 
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!

Meltad
Run time error '424' object required
 
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!

NickHK
Run time error '424' object required
 
From my code you are looping
For Each cell in rngAll
so you are looking at the Cell.Value

NickHK

"Meltad" wrote in message
...
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!

Meltad
Run time error '424' object required
 
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:C31,B32:C32,B33:C33,B34:C 34,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:C 47,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

NickHK
Run time error '424' object required
 
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

Meltad
Run time error '424' object required
 
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


All times are GMT +1. The time now is 09:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com