时间戳

首页 > 解决方案 > 循环遍历 WordsList 不能偏移每个集合的数据

vba - 循环遍历 WordsList 不能偏移每个集合的数据

问题描述

有点愚蠢的问题可能是一个简单的答案！

我正在尝试遍历数据，通过分隔符将其拆分，然后想将每个拆分部分偏移到行中，然后移至下一行/数据，

这是我的代码：

Dim undelimitedstring As String

    undelimitedstring = AlphaNumericOnly(rng.Value)

    WordsList = Split(undelimitedstring, "Q")

    result = ""
    Count = UBound(WordsList)

    For i = 1 To Count

        Dim ios As Integer
        ios = 0
        Worksheets("Calendar").Range("H" & rng.Row).Activate
        MsgBox (WordsList(i))

        For Each element In WordsList
            Dim n As Integer
            Dim o As Integer
            n = 0
            o = 1
            ActiveCell.Offset(0, n).Value = WordsList(o)
        Next element

    ios = ios + 1
    Next i

如果有人可以提供帮助，那就太好了！

标签： vbaexcelloopsfor-loop

解决方案

我相信以下内容会达到您的预期，它将获取 WordsList 中的所有值并将每个值输入到 H 列的新行中：

Dim undelimitedstring As String

undelimitedstring = AlphaNumericOnly(Rng.Value)

WordsList = Split(undelimitedstring, "Q")

result = ""

For i = 1 To UBound(WordsList)
    NextFreeRow= Worksheets("Calendar").Cells(Worksheets("Calendar").Rows.Count, "H").End(xlUp).Row + 1
    Worksheets("Calendar").Range("H" & NextFreeRow).Value = WordsList(i)
Next i

更新：

下面修改后的代码会将 WordList 中的两个值添加到相邻单元格中的一行，然后移动到下一行并添加接下来的两个值，依此类推：

Dim undelimitedstring As String

undelimitedstring = AlphaNumericOnly(Rng.Value)

WordsList = Split(undelimitedstring, "Q")

result = ""
NextFreeRow = Worksheets("Calendar").Cells(Worksheets("Calendar").Rows.Count, "H").End(xlUp).Row + 1
Counter = 0
For i = 1 To UBound(WordsList)
    Counter = Counter + 1
    Worksheets("Calendar").Range("H" & NextFreeRow).Value = WordsList(i)
    If Counter = 2 Then
    Worksheets("Calendar").Range("I" & NextFreeRow).Value = WordsList(i)
        NextFreeRow = NextFreeRow + 1
        Counter = 0
    End If
Next i

推荐阅读