Assigning Array to Non-Contiguous Range
Posted by Jimmy Rizos on July 19, 2001 12:43 PM
Can this be done?
ie
Range("B1,B5,B7,B10") = Array(5, 6, 8, 9)
thanks
jr
Posted by Leo Heuser on July 20, 2001 3:30 AM
Jimmy
This code will do the job
Best regards
LeoH
MVP - Excel
Sub ArrayToNonContigRange()
'leo.heuser@get2net.dk, 20. July 2001
Dim Numbers As Variant
Dim Area As Range
Dim rRange As Range
Dim Cell As Range
Dim Counter As Long
Numbers = Array(5, 6, 8, 9)
Set rRange = Range("B1,b5,b7,b10")
Counter = LBound(Numbers)
For Each Area In rRange.Areas
For Each Cell In Area
Cell.Value = Numbers(Counter)
Counter = Counter + 1
Next Cell
Next Area
End Sub
