使用VBA在Excel中向列输入多个文本

debugcn 发表于 Dev

玉精灵

抱歉，我是第一次尝试VBA，但不太确定如何对多列的文本执行文本操作。

我已在AN，AO和AP列中设置了值。

我想：1）将第AN列的文本输入AQ-AZ

2）将文本从AO列转换为BA-BE列

3）将文本从AP列转换为BF-BJ列

我已经以这种方式启动了我的代码，但是无论我尝试什么范围，都向我显示错误：

Dim count As Integer

count = Worksheets("Sheet0").Range("A1", 
Worksheets("Sheet0").Range("A1").End(xlDown)).Rows.Count - 1

For i = 1 To count

???? 接下来发生什么？

我在这些列中有空单元格，希望对列执行文本操作，因此我引用的是列A，这是我的数据的完整列表

病毒

这是一个快速示例：

_{样本数据：}

或在您的特定情况下：

_{样例代码：}

Option Explicit

Sub Sample()

Dim lr As Long
Dim rng As Range, Area as Range

With Sheet1 'Change according to your sheets CodeName
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng = .Range(Replace("B2:B?,F2:F?,J2:J?", "?", lr))
    For Each Area In rng.Areas
        Area.TextToColumns Destination:=Area.Offset(0, 1).Resize(Area.Rows.Count, 3), DataType:=xlDelimited, Comma:=True
    Next Area
End With

End Sub

另一个可能更容易理解的方法可能是：

Option Explicit

Sub Sample()

Dim lr As Long, x As Long
Dim rng1 As Range, rng2 As Range

With Sheet1 'Change according to your sheets CodeName
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng1 = .Range(Replace("B2:B?,F2:F?,J2:J?", "?", lr))
    Set rng2 = .Range(Replace("C2:E?,G2:I?,K2:M?", "?", lr))
    For x = 1 To rng1.Areas.Count
        rng1.Areas(x).TextToColumns Destination:=rng2.Areas(x), DataType:=xlDelimited, Comma:=True
    Next x
End With

End Sub

或在您的特定情况下：

Option Explicit

Sub Sample()

Dim lr As Long, x As Long
Dim rng1 As Range, rng2 As Range

With Sheet1 'Change according to your sheets CodeName
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rng1 = .Range(Replace("AN2:AN?,AO2:AO?,AP2:AP?", "?", lr))
    Set rng2 = .Range(Replace("AQ2:AZ?,BA2:BE?,BF2:BJ?", "?", lr))
    For x = 1 To rng1.Areas.Count
        rng1.Areas(x).TextToColumns Destination:=rng2.Areas(x), DataType:=xlDelimited, Comma:=True
    Next x
End With

End Sub

_{样本结果：}