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!

  #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!



  #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!




  #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!






  #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!









  #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!









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

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



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 98
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 03:15 AM.

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"