semicolon delimiter in a cell

In a cell I have this values

;1,LPT,23;2,5;DD;5,5;ABC;6,5; etc......

How can I, using VB assign to an array-variable;
(semicolon delimiter ";")

Thanks

Coco

Dim ary

ary = Split(ActiveCell.Value, ";")

--
HTH

Bob Phillips

"coco" <[email protected]> wrote in message
news:[email protected]...
> In a cell I have this values
>
> ;1,LPT,23;2,5;DD;5,5;ABC;6,5; etc......
>
> How can I, using VB assign to an array-variable;
> (semicolon delimiter ";")
>
> Thanks
>
> Coco
>
>

Something like...

Option Explicit
Sub testme()

Dim myVal As String
Dim mySplit As Variant
Dim iCtr As Long
myVal = ActiveSheet.Range("a1").Value

mySplit = Split(myVal, ";")
'mySplit = Split97(myVal, ";")

For iCtr = LBound(mySplit) To UBound(mySplit)
Debug.Print iCtr & "--" & mySplit(iCtr)
Next iCtr

End Sub
'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
Split was added in xl2k. If you're using xl97, use Tom's Split97.

coco wrote:
>
> In a cell I have this values
>
> ;1,LPT,23;2,5;DD;5,5;ABC;6,5; etc......
>
> How can I, using VB assign to an array-variable;
> (semicolon delimiter ";")
>
> Thanks
>
> Coco

--

Dave Peterson