要在每第三列上执行“文本到列”

I am trying to iterate through the columns of a spreadsheet and split every third column at the comma. My data looks something like this:

我尝试遍历电子表格的列，并在逗号处分割每三列。我的数据是这样的:

coordinates   (blank)    name
x1,y1                    object 1
x2,y2                    object 2

And I am trying to turn it to this using a macro script:

我试着用一个宏脚本把它变成这个

coordinates   (blank)    name
x1            y1         object 1
x2            y2         object 2

I know I will need a loop that performs split on a column, then goes to the one three columns away, and repeats. The following is my attempt, but nothing happens when I run it!

我知道我需要一个循环，在一个列上执行分割，然后转到三列之外的一个，然后重复。下面是我的尝试，但是当我运行它时什么也没有发生!

Sub SplitEveryThird(MyRange As Range)
    Dim x As Integer
    SplitEveryThird = 0
    For x = 1 To MyRange.Cells.Count
      If (x Mod 3) = 1 Then
        Dim objRange1 As Range

             'Split column at the comma
              MyRange.TextToColumns _
                   Destination:=Range("A2"), _
                   DataType:=xlDelimited, _
                   Tab:=False, _
                   Semicolon:=False, _
                   Comma:=True, _
                   Space:=False, _
                   Other:=False, _
                   OtherChar:="-"
    End If
  Next x
End Sub

2 个解决方案

#1

Here goes my answer:

这是我的回答:

Sub test()
    SplitEveryThird_2 Range("A1:O1")
End Sub

Sub SplitEveryThird_2(rng As Range)
    'where:
    'rng is only the 1 rows of the columns with the data
    'the address of rng would be A1:O1 in my case

    Dim x As Integer
    Dim r 'to store the last row of the column
    Dim theCol As Range

    For x = 1 To rng.Count
      If (x Mod 3) = 1 Then
        r = Range(Cells(1, x), Cells(1, x)).End(xlDown).Row
        'this is to take the last row of the column
        'you could use this:

        'r = range(cells(1048576,x),cells(1048576,x)).End(xlUp).Row

        'gives you the same result, with the difference that if the range
        'with the data has empty rows, would be no problem
        Set theCol = Range(Cells(1, x), Cells(r, x))
             'Split column at the comma

                'here you say to put the result in the first row of the
                'selected column, not just in A1
              theCol.TextToColumns _
                   Destination:=Range(Cells(1, x), Cells(1, x)), _
                   DataType:=xlDelimited, _
                   Tab:=False, _
                   Semicolon:=False, _
                   Comma:=True, _
                   Space:=False, _
                   Other:=False, _
                   OtherChar:="-"
    End If
    Set theCol = Nothing
  Next x
End Sub

Example data:

示例数据:

+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+
| X1,Y1   |  | Object1  | X1,Y1   |  | Object1  | X1,Y1   |  | Object1  | X1,Y1   |  | Object1  | X1,Y1   |  | Object1  |
| X2,Y2   |  | Object2  | X2,Y2   |  | Object2  | X2,Y2   |  | Object2  | X2,Y2   |  | Object2  | X2,Y2   |  | Object2  |
| X3,Y3   |  | Object3  | X3,Y3   |  | Object3  | X3,Y3   |  | Object3  | X3,Y3   |  | Object3  | X3,Y3   |  | Object3  |
| X4,Y4   |  | Object4  | X4,Y4   |  | Object4  | X4,Y4   |  | Object4  | X4,Y4   |  | Object4  | X4,Y4   |  | Object4  |
| X5,Y5   |  | Object5  | X5,Y5   |  | Object5  | X5,Y5   |  | Object5  | X5,Y5   |  | Object5  | X5,Y5   |  | Object5  |
| X6,Y6   |  | Object6  | X6,Y6   |  | Object6  | X6,Y6   |  | Object6  | X6,Y6   |  | Object6  | X6,Y6   |  | Object6  |
| X7,Y7   |  | Object7  | X7,Y7   |  | Object7  | X7,Y7   |  | Object7  | X7,Y7   |  | Object7  | X7,Y7   |  | Object7  |
| X8,Y8   |  | Object8  | X8,Y8   |  | Object8  | X8,Y8   |  | Object8  | X8,Y8   |  | Object8  | X8,Y8   |  | Object8  |
| X9,Y9   |  | Object9  | X9,Y9   |  | Object9  | X9,Y9   |  | Object9  | X9,Y9   |  | Object9  | X9,Y9   |  | Object9  |
| X10,Y10 |  | Object10 | X10,Y10 |  | Object10 | X10,Y10 |  | Object10 | X10,Y10 |  | Object10 | X10,Y10 |  | Object10 |
| X11,Y11 |  | Object11 | X11,Y11 |  | Object11 | X11,Y11 |  | Object11 | X11,Y11 |  | Object11 | X11,Y11 |  | Object11 |
| X12,Y12 |  | Object12 | X12,Y12 |  | Object12 | X12,Y12 |  | Object12 | X12,Y12 |  | Object12 | X12,Y12 |  | Object12 |
| X13,Y13 |  | Object13 | X13,Y13 |  | Object13 | X13,Y13 |  | Object13 | X13,Y13 |  | Object13 | X13,Y13 |  | Object13 |
| X14,Y14 |  | Object14 | X14,Y14 |  | Object14 | X14,Y14 |  | Object14 | X14,Y14 |  | Object14 | X14,Y14 |  | Object14 |
| X15,Y15 |  | Object15 | X15,Y15 |  | Object15 | X15,Y15 |  | Object15 | X15,Y15 |  | Object15 | X15,Y15 |  | Object15 |
| X16,Y16 |  | Object16 | X16,Y16 |  | Object16 | X16,Y16 |  | Object16 | X16,Y16 |  | Object16 | X16,Y16 |  | Object16 |
| X17,Y17 |  | Object17 | X17,Y17 |  | Object17 | X17,Y17 |  | Object17 | X17,Y17 |  | Object17 | X17,Y17 |  | Object17 |
| X18,Y18 |  | Object18 | X18,Y18 |  | Object18 | X18,Y18 |  | Object18 | X18,Y18 |  | Object18 | X18,Y18 |  | Object18 |
| X19,Y19 |  | Object19 | X19,Y19 |  | Object19 | X19,Y19 |  | Object19 | X19,Y19 |  | Object19 | X19,Y19 |  | Object19 |
| X20,Y20 |  | Object20 | X20,Y20 |  | Object20 | X20,Y20 |  | Object20 | X20,Y20 |  | Object20 | X20,Y20 |  | Object20 |
| X21,Y21 |  | Object21 | X21,Y21 |  | Object21 | X21,Y21 |  | Object21 | X21,Y21 |  | Object21 | X21,Y21 |  | Object21 |
| X22,Y22 |  | Object22 | X22,Y22 |  | Object22 | X22,Y22 |  | Object22 | X22,Y22 |  | Object22 | X22,Y22 |  | Object22 |
| X23,Y23 |  | Object23 | X23,Y23 |  | Object23 | X23,Y23 |  | Object23 | X23,Y23 |  | Object23 | X23,Y23 |  | Object23 |
| X24,Y24 |  | Object24 | X24,Y24 |  | Object24 | X24,Y24 |  | Object24 | X24,Y24 |  | Object24 | X24,Y24 |  | Object24 |
+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+

This is the result:

这是由于:

+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+
| X1  | Y1  | Object1  | X1  | Y1  | Object1  | X1  | Y1  | Object1  | X1  | Y1  | Object1  | X1  | Y1  | Object1  |
| X2  | Y2  | Object2  | X2  | Y2  | Object2  | X2  | Y2  | Object2  | X2  | Y2  | Object2  | X2  | Y2  | Object2  |
| X3  | Y3  | Object3  | X3  | Y3  | Object3  | X3  | Y3  | Object3  | X3  | Y3  | Object3  | X3  | Y3  | Object3  |
| X4  | Y4  | Object4  | X4  | Y4  | Object4  | X4  | Y4  | Object4  | X4  | Y4  | Object4  | X4  | Y4  | Object4  |
| X5  | Y5  | Object5  | X5  | Y5  | Object5  | X5  | Y5  | Object5  | X5  | Y5  | Object5  | X5  | Y5  | Object5  |
| X6  | Y6  | Object6  | X6  | Y6  | Object6  | X6  | Y6  | Object6  | X6  | Y6  | Object6  | X6  | Y6  | Object6  |
| X7  | Y7  | Object7  | X7  | Y7  | Object7  | X7  | Y7  | Object7  | X7  | Y7  | Object7  | X7  | Y7  | Object7  |
| X8  | Y8  | Object8  | X8  | Y8  | Object8  | X8  | Y8  | Object8  | X8  | Y8  | Object8  | X8  | Y8  | Object8  |
| X9  | Y9  | Object9  | X9  | Y9  | Object9  | X9  | Y9  | Object9  | X9  | Y9  | Object9  | X9  | Y9  | Object9  |
| X10 | Y10 | Object10 | X10 | Y10 | Object10 | X10 | Y10 | Object10 | X10 | Y10 | Object10 | X10 | Y10 | Object10 |
| X11 | Y11 | Object11 | X11 | Y11 | Object11 | X11 | Y11 | Object11 | X11 | Y11 | Object11 | X11 | Y11 | Object11 |
| X12 | Y12 | Object12 | X12 | Y12 | Object12 | X12 | Y12 | Object12 | X12 | Y12 | Object12 | X12 | Y12 | Object12 |
| X13 | Y13 | Object13 | X13 | Y13 | Object13 | X13 | Y13 | Object13 | X13 | Y13 | Object13 | X13 | Y13 | Object13 |
| X14 | Y14 | Object14 | X14 | Y14 | Object14 | X14 | Y14 | Object14 | X14 | Y14 | Object14 | X14 | Y14 | Object14 |
| X15 | Y15 | Object15 | X15 | Y15 | Object15 | X15 | Y15 | Object15 | X15 | Y15 | Object15 | X15 | Y15 | Object15 |
| X16 | Y16 | Object16 | X16 | Y16 | Object16 | X16 | Y16 | Object16 | X16 | Y16 | Object16 | X16 | Y16 | Object16 |
| X17 | Y17 | Object17 | X17 | Y17 | Object17 | X17 | Y17 | Object17 | X17 | Y17 | Object17 | X17 | Y17 | Object17 |
| X18 | Y18 | Object18 | X18 | Y18 | Object18 | X18 | Y18 | Object18 | X18 | Y18 | Object18 | X18 | Y18 | Object18 |
| X19 | Y19 | Object19 | X19 | Y19 | Object19 | X19 | Y19 | Object19 | X19 | Y19 | Object19 | X19 | Y19 | Object19 |
| X20 | Y20 | Object20 | X20 | Y20 | Object20 | X20 | Y20 | Object20 | X20 | Y20 | Object20 | X20 | Y20 | Object20 |
| X21 | Y21 | Object21 | X21 | Y21 | Object21 | X21 | Y21 | Object21 | X21 | Y21 | Object21 | X21 | Y21 | Object21 |
| X22 | Y22 | Object22 | X22 | Y22 | Object22 | X22 | Y22 | Object22 | X22 | Y22 | Object22 | X22 | Y22 | Object22 |
| X23 | Y23 | Object23 | X23 | Y23 | Object23 | X23 | Y23 | Object23 | X23 | Y23 | Object23 | X23 | Y23 | Object23 |
| X24 | Y24 | Object24 | X24 | Y24 | Object24 | X24 | Y24 | Object24 | X24 | Y24 | Object24 | X24 | Y24 | Object24 |
+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+

Hope this help you.

希望这对你有所帮助。

#2

You are passing in a parameter but also treating the sub as a function by assigning a value to it. Functions are not intended to perform actions on cells; best to leave that to sub procedures. If you require a return value like ERROR_SUCCESS then pass another parameter into the sub using ByRef and change its value according to the outcome.

您正在传递一个参数，但也通过为其赋值将子函数视为一个函数。函数不打算对单元格执行操作;最好把它留给子过程。如果您需要一个返回值，比如ERROR_SUCCESS，那么使用ByRef将另一个参数传递给子参数，并根据结果更改它的值。

Option Explicit

Sub test()
    With Worksheets("Sheet1")
        SplitEveryThird_2 .Range("A1:O1")
    End With
End Sub

Sub SplitEveryThird_2(rng As Range)
    Dim c As Long

    Application.DisplayAlerts = False

    With rng.EntireColumn
        For c = 1 To .Columns.Count Step 3
            'Split column at the comma
            If CBool(Application.CountA(.Columns(c))) Then
                .Columns(c).TextToColumns Destination:=.Cells(1, c), _
                     DataType:=xlDelimited, Comma:=True, _
                     Tab:=False, Semicolon:=False, _
                     Space:=False, Other:=False, OtherChar:=vbNullString
            End If
        Next c
    End With
End Sub

#1