VBA 拆分字符串循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24989043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Split String Loop
提问by LearnForever
I am trying to split a string and create a loop for going through the cells in the column.There are a few challenges:
我正在尝试拆分一个字符串并创建一个循环来遍历列中的单元格。有一些挑战：
Split works for
ActiveCellonly.Loop goes through all cells until LastRow but populates all cells with split string values from
ActiveCellonly.Split of Array starts with
i = 0even though there isOption Base 1at the beginning of the Module.How can I change the location of destination (e.g. instead of splitting string next to existing data, is there an option to manage column numbers)?
拆分
ActiveCell仅适用于。循环遍历所有单元格，直到 LastRow，但
ActiveCell仅使用拆分字符串值填充所有单元格。i = 0即使Option Base 1在模块的开头有Array 的拆分也开始。如何更改目标位置（例如，不是在现有数据旁边拆分字符串，是否有管理列号的选项）？
Thank you
谢谢
Option Explicit
Option Base 1
Sub SplitStringLoop()
Dim txt As String
Dim i As Integer
Dim y As Integer
Dim FullName As Variant
Dim LastRow As Single
ReDim FullName(3)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
txt = ActiveCell.Value
FullName = Split(txt, "-")
For y = 2 To LastRow
For i = 1 To UBound(FullName)
Cells(y, i + 1).Value = FullName(i)
Next i
Next y
End Sub
回答by RowanC
Chris Nelisen outlined the reasons, I had this code written before he posted, so I'll post it anyway.
Chris Nelisen 概述了原因，我在他发布之前编写了这段代码，所以无论如何我都会发布它。
Option Explicit
Sub SplitStringLoop()
Dim txt As String
Dim i As Integer
Dim y As Integer
Dim FullName As Variant
Dim LastRow As Single
ReDim FullName(3)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For y = 2 To LastRow
Cells(y, 1).Select
txt = ActiveCell.Value
FullName = Split(txt, "-")
For i = 0 To UBound(FullName)
Cells(y, i + 2).Value = FullName(i)
Next i
Next
End Sub
回答by chris neilsen
To address the issues you list
解决您列出的问题
Splitacts on the string you pass to it. Youare passing the active cell value to it.- You don't update the result of split (
FullName) inside the loop. So what else do you expect? SplitReturns a zero-based, one-dimensional array. It says so right there in the help.Option Base 1specifies the defaultlower bound, for when you don't specify it in aDimstatement.- You are specifying the column in your code
Cells(y, i + 1)(i + 1in this case). If you want it somewhere else, specify a different column.
Split作用于您传递给它的字符串。 您正在将活动单元格值传递给它。- 您不会
FullName在循环内更新 split ( )的结果。那么你还有什么期待呢？ Split返回一个从零开始的一维数组。它在帮助中这么说。Option Base 1指定默认下限，因为当您没有在Dim语句中指定它时。- 您正在代码中指定列
Cells(y, i + 1)（i + 1在本例中）。如果您想在其他地方使用它，请指定不同的列。
