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.
|