array filling: cell addresses

[Peter]

Hello everybody,

I'm trying to fill an array with the cell addresses of several separately
selected cells.

This is what I have so far:

........
Option Explicit

Sub test()
Dim cell As Range
Dim CellAdresses() As Variant
For Each cell In Selection
CellAdresses(cell) = cell.Address
Next cell
End Sub
......

Doing this gets me error number 9 Subscript out of range.

It's propably a simple thing I'm overlooking. Please help.

Peter.

 

[Tom Ogilvy]

Option Explicit

Sub test()
Dim cell As Range
Dim CellAdresses() As Variant
Dim i as Long
Redim cellAdresses(1 to selection.Count)
i = 0
For Each cell In Selection
i = i + 1
CellAdresses(i) = cell.Address
Next cell
End Sub

 

[Dana DeLouis]

If the selections are truly individual cells, would this idea help?

Sub Demo()
Dim CellAdresses
Range("A1,B3,C5,D4,E3,F2,G1").Select

CellAdresses = Split(Selection.Address(False, False), ",")
End Sub

 

[Peter]

Thanks Tom, that's what I needed.

Peter

Option Explicit

Sub test()
Dim cell As Range
Dim CellAdresses() As Variant
Dim i as Long
Redim cellAdresses(1 to selection.Count)
i = 0
For Each cell In Selection
i = i + 1
CellAdresses(i) = cell.Address
Next cell
End Sub