MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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