VBA分割字符串循环 [英] VBA Split String Loop

问题描述

我正在尝试拆分字符串并创建循环以遍历该列中的单元格.存在一些挑战:

I am trying to split a string and create a loop for going through the cells in the column.There are a few challenges:

1. 拆分仅适用于 ActiveCell .

循环遍历所有单元格直到LastRow，然后填充所有单元格仅包含来自 ActiveCell 的字符串拆分值.

Loop goes through all cells until LastRow but populates all cells with split string values from ActiveCell only.

即使存在 Option Base 1 ，数组拆分也从 i = 0 开始在模块的开头.

Split of Array starts with i = 0 even though there is Option Base 1 at 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)?

谢谢

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

推荐答案

Chris Nelisen概述了原因，我在他发布代码之前就已经编写了这段代码，所以我还是要发布它.

Chris Nelisen outlined the reasons, I had this code written before he posted, so I'll post it anyway.

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

这篇关于VBA分割字符串循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！