array filling: cell addresses
 

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.

array filling: cell addresses
 

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


--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
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.

array filling: cell addresses
 

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

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Peter" wrote in message
...
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.

array filling: cell addresses
 

Thanks Tom, that's what I needed.

Peter


"Tom Ogilvy" schreef in bericht
...
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


--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
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.