excel - 需要帮助使用此循环拆分脚本定义范围和字符串

Question

我在 C 列中有一个数据列表，我希望将此拆分函数的输出放入 D 列。到目前为止我编写的脚本是:

 Sub SplitTest()
     Dim txt As String
     Dim Rng As Range
     Dim i As Integer
     Dim Authors As Variant
     i = 2
     Set Rng = Range("C" & i)
     txt = Rng.Value
     Authors = Split(txt, ["."])

    For i = 2 To UBound(Authors)
        Set Rng = Range("C" & i)
        Range("D" & i + 1).Value = Authors(i)
        Next i
    End Sub

现在，脚本只运行第 3 到第 5 行。 Range("D"... 有什么问题吗？线？

提前致谢

Answer 1

您需要先设置范围，然后才能使用它。

Dim Rng As Range
Set Rng = Range("C" & i)
txt = Rng.Value

此外，您需要先设置 i = 一些行号才能使用它。你现在有

Dim i As Integer
Dim Authors As Variant
Set Rng = Range("C" & i)

但我还没准备好。你需要设置它。

Dim i As Integer
Dim Authors As Variant
i = 1
Set Rng = Range("C" & i)

编辑:根据 OP 评论。

看看把所有这些放在行上的循环中。

Sub SplitTest()
    Dim ws As Excel.Worksheet
    Dim lRow As Long

    Dim txt As String
    Dim Rng As Range
    Dim i As Integer
    Dim Authors As Variant
    Dim strColumn As String

    Set ws = Application.ActiveSheet

    lRow = 1

    'Loop through process each row.
    Do While lRow <= ws.UsedRange.Rows.count

        Set Rng = Range("C" & lRow)

        txt = Rng.Value
        Authors = Split(txt, ["."])

        'Loop through the split results and put them in column to the right
        For i = 0 To UBound(Authors)
            strColumn = Col_Letter(i + 4)
            ws.Range(strColumn & lRow) = Authors(i)
        Next i

        lRow = lRow + 1
    Loop
End Sub

添加此功能

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function