Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range collection
Hello,
Is ther a limit to the # of cells one can use in a range collection? IE: For Each rCell In Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next This does not work... Ernst. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range collection
I recommend instead managing that massive list in a String array, and
for j = ubound(strAddr) to 1 range(strAddr(j)).Value = ... As to "This does not work" a possible culprit is that line width, which is excessive. If anyone reads your message in a web browser it will possibly be unpleasant to view. When you have a string that long, use the underscore(_) for line continuation; you have the ampersand(&) operator if you really need that long of a string. A reasonable rule of thumb with long strings would be to line-continue after 60-70 characters. Excel limits are at http://office.microsoft.com/en-us/as...992911033.aspx though I see and suspect no limit on range sizes, per se. You're perfectly free to put a value in IV65536 and Select All so a range limit is unlikely. Unless I'm mistaken, of course :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range collection
Hi. Just a another guess, but Excel may need to know what "Area" of your
range you are referring to. In general, C17:Q17 is Area 1, S17:T17 is area 2, etc. Not tested, but here's the general idea: Dim BigArea As Range Dim Rng As Range Dim Cell As Range Set BigArea = Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7") '...etc For Each Rng In BigArea.Areas For Each Cell In Rng.Cells Cell.Value = rData(i) i = i + 1 Next Cell Next Rng HTH -- Dana DeLouis Win XP & Office 2003 "Ernst Guckel" wrote in message ... Hello, Is ther a limit to the # of cells one can use in a range collection? IE: For Each rCell In Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next This does not work... Ernst. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range collection
How does it not work?
Also, you may be better off recognizing and leveraging the patterns in the layout of the various regions. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hello, Is ther a limit to the # of cells one can use in a range collection? IE: For Each rCell In Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next This does not work... Ernst. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range collection
This worked fine for me
(I broke up you string just so it would paste better into the post) Sub ABC() Dim rData(1 To 2000) As Long For i = 1 To 2000 rData(i) = Int(Rnd() * 10000 + 1) Next i = 1 For Each rCell In Range("C17:Q17,S17:T17,V17:AC17" & _ ",AH17:AO17,AQ17:AR17,C21:Q27,S21:T27,V21:AC27 ," & _ "AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37, " & _ "AH31:AO37,AQ31:AR37,C41:Q47,S41:T47,V41:AC47, " & _ "AH41:AO47,AQ41:AR47,C51:Q57,S51:T57,V51:AC57, " & _ "AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V62:AC63, " & _ "AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next Debug.Print i End Sub So this version worded as well also: Sub ABC() Dim rData(1 To 2000) As Long For i = 1 To 2000 rData(i) = Int(Rnd() * 10000 + 1) Next i = 1 For Each rCell In Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27 ,AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37,AH31 :AO37,AQ31:AR37,C41:Q47,S4 1:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T57 ,V51:AC57,AH51:AO57,AQ51:A R57,C62:Q63,S62:T63,V62:AC63,AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next Debug.Print i End Sub -- Regards, Tom Ogilvy "Ernst Guckel" wrote in message ... Hello, Is ther a limit to the # of cells one can use in a range collection? IE: For Each rCell In Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27 ,AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37,AH31 :AO37,AQ31:AR37,C41:Q47,S4 1:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T57 ,V51:AC57,AH51:AO57,AQ51:A R57,C62:Q63,S62:T63,V62:AC63,AH62:AO63,AQ62:AR63") rCell.Value = rData(i) i = i + 1 Next This does not work... Ernst. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collection | Excel Programming | |||
Range objects in a collection | Excel Programming | |||
Deleting named ranges by looping through range collection | Excel Programming | |||
Adding Range to Worksheet Names Collection | Excel Programming | |||
calculating and adding a range to a collection | Excel Programming |