fill array from active cell

[christinalou]

I have tried to find how to do this from previous posts but my limited
programming skills have not been able to accomplish it.

What I need to do, is take a cell in excel with data and put into an array

i.e. Cell data contains 3,6,8 and I need an 1x3 array arrary(1)=3
array(2)=6, array(3)=8

I want to be able to select a cell and then run the macro

Here is what I have so far (it doesnt work):

Sub FindAreas()

Dim areas()


areas = Split(ActiveCell.Value, ",")

Range("B1", "B3").Select
For i = 1 To 3
ActiveCell(i) = areas(i)
Next i

End Sub

 

[Jim Thomlinson]

Try this perhaps...

Sub Test()
Dim var As Variant
Dim lng As Long

var = Split(ActiveCell.Value, ",")
For lng = LBound(var) To UBound(var)
ActiveCell.Offset(0, lng + 1).Value = var(lng)
Next lng
End Sub

 

[Tom Hutchins]

Try this:

Sub FindAreas()
Dim i As Long, areas
areas = Split(ActiveCell.Value, ",")
Range("B1").Select
For i = 0 To UBound(areas)
ActiveCell.Cells(i + 1, 1) = areas(i)
Next i
End Sub

Hope this helps,

Hutch

 

[Alan Beban]

I have tried to find how to do this from previous posts but my limited
programming skills have not been able to accomplish it.

What I need to do, is take a cell in excel with data and put into an array

i.e. Cell data contains 3,6,8 and I need an 1x3 array arrary(1)=3
array(2)=6, array(3)=8

I want to be able to select a cell and then run the macro

Here is what I have so far (it doesnt work):

Sub FindAreas()

Dim areas()


areas = Split(ActiveCell.Value, ",")

Range("B1", "B3").Select
For i = 1 To 3
ActiveCell(i) = areas(i)
Next i

End Sub

Sub FindAreas()
Range(ActiveCell(1, 2), ActiveCell(1, 4)).Value = Split(ActiveCell, ",")
End Sub