Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run time error '424' object required
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 98
Default 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!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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!



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 98
Default 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!




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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!






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 98
Default 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!







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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!









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime error '424': Object Required loren.pottinger Excel Discussion (Misc queries) 1 August 28th 06 09:56 PM
"Microsoft Visual Basic runtime error '424' object required". SharonG. Excel Worksheet Functions 0 July 5th 06 01:36 AM
Excel 2007 Beta 2 - Macro Run-time error '424' Object required jcm21 Excel Programming 0 June 16th 06 07:17 PM
Run-time error '424': Object required Phil Bewig Excel Programming 3 February 1st 04 08:38 PM
Syntax Error Runtime Error '424' Object Required sjenks183 Excel Programming 1 January 23rd 04 09:25 AM


All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement