cell contents into an array

[Gary Keramidas]

i have a column with acct #s in it. each cell can have multiple accts. they are
separated by a comma. how do i get these into an array so i can access each acct
# as an element?

i tried using arr=array(range("E3").value, but it created 1 element with the 3
acct #'s. tried to use split, but it only brought in the first one.

there may be only 1 acct #, there may be 15 in ach cell.

 

[Gary Keramidas]

i think i got it

 

[Steve Yandl]

Gary,

The split function should work but it needs to be fed a text string, not a
numerical value. Unfortunately, if you just select the column and use
'Format > Cells', the numbers will be converted to text but Excel will drop
the commas (at least it did when I tested on my system). I'd copy the
column, to a new column already formatted as text or even copy, format the
current column and paste the values over the old ones but where the cells
are formatted as text (that should convert to a text string and retain the
commas).

This sub should work:

Sub ArrayFromCell()
arrCell = Split(Range("E3").Value, ",")
For A = 0 To UBound(arrCell)
MsgBox arrCell(A)
Next A
End Sub

Steve