Split Cells in Excel 2010 using VBA 1

[JasonEnsor]

Hiya,

I am currently trying to process some data in excel that i need to split in to multiple rows. I have the following piece of code that will loop through the data and split it at the delimeter however i want the value before the delimeter in A2 for example and the value after the delimeter in B2. This currently only gets the data before the delimeter

 Do Until IsEmpty(ActiveCell)
        
        X = Split(ActiveCell.Value, "-")
        ActiveCell.Offset(, -1).Resize(UBound(X)).Value = Application.Transpose(X)
        ActiveCell.Value = X
        ActiveCell.Offset(1, 0).Select

      Loop

Any ideas, really struggling with this?

Many thanks in advance

Jason

 

[SkipVought]

hi,

My advise: AVOID the Select and Activate methods as a programming method!

    Dim r As Range, X
    
    For Each r In Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
        X = Split(r.Value, "-")
        r.Value = X(0)
        r.Offset(0, 1).Value = X(1)
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

As always Skip you have solved my problem.

Many Thanks

 

[SkipVought]

And a more general approch for arrays...

    Dim r As Range, X, i As Integer
    
    For Each r In Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
        X = Split(r.Value, "-")
        For i = 0 To UBound(X)
            Cells(r.Row, i + 1).Value = X(i)
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}