Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
"Microsoft Visual Basic runtime error '424' object required". | Excel Worksheet Functions | |||
Excel 2007 Beta 2 - Macro Run-time error '424' Object required | Excel Programming | |||
Run-time error '424': Object required | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |