1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • SplitData

    From Saxman@21:1/5 to All on Sun Feb 2 13:12:23 2020
    Klaus Busch who is active on the group kindly wrote some code for me
    awhile back.

    Basically it converts the following data,

    4/7 Aspire Tower, 9/2 Cerberus, 13/2 Wolf Prince, 12/1 A Wave Of The
    Sea, 16/1 Never Do Nothing, 20/1 Hammersmith, 25/1 Clemencia, Oak Park,
    50/1 Takarengo, 100/1 Three Comets.

    into this format in seperate cells.

    Aspire Tower 4/7
    Cerbus 13/2
    Wolf Prince 13/2

    etc.....

    It worked fine, but now I get a syntax error on the following line.

    .Range("B2").Resize(UBound(varOut, 2), 2) =

    I cannot think why, but I'm not that code savvy.

    The full code is below.

    Option Explicit

    Sub SplitData()
    Dim LRow As Long, i As Long, n As Long
    Dim j As Integer
    Dim varData As Variant, varTmp As Variant, varOut() As Variant

    n = 1
    With ActiveSheet
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & LRow).Replace Chr(160), Chr(32)
    varData = .Range("A2:A" & LRow)
    For i = LBound(varData) To UBound(varData)
    varTmp = Split(varData(i, 1), ",")
    For j = LBound(varTmp) To UBound(varTmp)
    ReDim Preserve varOut(1 To 2, 1 To n)
    If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
    varOut(1, n) = Mid(Trim(varTmp(j)),
    InStr(Trim(varTmp(j)), " ") + 1)
    varOut(2, n) = CStr(Trim(Replace(varTmp(j), varOut(1,
    n), "")))
    Else
    varOut(1, n) = Trim(varTmp(j))
    varOut(2, n) = varOut(2, n - 1)
    End If
    n = n + 1
    Next
    Next
    .Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
    .Range("B2").Resize(UBound(varOut, 2), 2) =
    Application.Transpose (varOut)
    End With
    End Sub

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sun Feb 2 17:50:51 2020
    Hi,

    Am Sun, 2 Feb 2020 13:12:23 +0000 schrieb Saxman:

    4/7 Aspire Tower, 9/2 Cerberus, 13/2 Wolf Prince, 12/1 A Wave Of The
    Sea, 16/1 Never Do Nothing, 20/1 Hammersmith, 25/1 Clemencia, Oak Park,
    50/1 Takarengo, 100/1 Three Comets.

    into this format in seperate cells.

    Aspire Tower 4/7
    Cerbus 13/2
    Wolf Prince 13/2

    etc.....

    perhaps an issue because of word wrap.

    Try it again:

    Sub SplitData()
    Dim LRow As Long, i As Long, n As Long
    Dim j As Integer
    Dim varData As Variant, varTmp As Variant, varOut() As Variant

    n = 1
    With ActiveSheet
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & LRow).Replace Chr(160), Chr(32)
    varData = .Range("A2:A" & LRow)
    For i = LBound(varData) To UBound(varData)
    varTmp = Split(varData(i, 1), ",")
    For j = LBound(varTmp) To UBound(varTmp)
    ReDim Preserve varOut(1 To 2, 1 To n)
    If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
    varOut(1, n) = Mid(Trim(varTmp(j)), _
    InStr(Trim(varTmp(j)), " ") + 1)
    varOut(2, n) = CStr(Trim(Replace(varTmp(j), _
    varOut(1, n), "")))
    Else
    varOut(1, n) = Trim(varTmp(j))
    varOut(2, n) = varOut(2, n - 1)
    End If
    n = n + 1
    Next
    Next
    .Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
    .Range("B2").Resize(UBound(varOut, 2), 2) = _
    Application.Transpose(varOut)
    End With
    End Sub


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Saxman@21:1/5 to Claus Busch on Mon Feb 3 10:46:25 2020
    On 02/02/2020 16:50, Claus Busch wrote:
    Sub SplitData()
    Dim LRow As Long, i As Long, n As Long
    Dim j As Integer
    Dim varData As Variant, varTmp As Variant, varOut() As Variant

    n = 1
    With ActiveSheet
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & LRow).Replace Chr(160), Chr(32)
    varData = .Range("A2:A" & LRow)
    For i = LBound(varData) To UBound(varData)
    varTmp = Split(varData(i, 1), ",")
    For j = LBound(varTmp) To UBound(varTmp)
    ReDim Preserve varOut(1 To 2, 1 To n)
    If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
    varOut(1, n) = Mid(Trim(varTmp(j)), _
    InStr(Trim(varTmp(j)), " ") + 1)
    varOut(2, n) = CStr(Trim(Replace(varTmp(j), _
    varOut(1, n), "")))
    Else
    varOut(1, n) = Trim(varTmp(j))
    varOut(2, n) = varOut(2, n - 1)
    End If
    n = n + 1
    Next
    Next
    .Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
    .Range("B2").Resize(UBound(varOut, 2), 2) = _
    Application.Transpose(varOut)
    End With
    End Sub


    That works fine Claus.

    Thank you very much.

    Enjoy your day.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)