我正在尝试遍历电子表格的列，并在逗号处每隔三列拆分一次。我的数据看起来像这样：
coordinates (blank) name
x1,y1 object 1
x2,y2 object 2
我正试图用一个宏脚本将其转化为这个：
coordinates (blank) name
x1 y1 object 1
x2 y2 object 2
我知道我需要一个循环，对一列执行拆分，然后转到三列之外的一列，然后重复。以下是我的尝试，但当我运行它时没有发生任何事情！
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
下面是我的答案：
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
示例数据：
+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+
| 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 |
+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+---------+--+----------+
这就是结果：
+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+
| 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 |
+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+-----+-----+----------+
希望这对你有所帮助。
您传递的是一个参数，但也通过为子函数赋值将其视为一个函数。函数不用于对单元格执行操作；最好将其留给子过程。如果需要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