VBA 拆分() 函数在":"是分隔符时不起作用

Question

我正试图使用split((函数在指定的范围内循环，并在"："遇到，并将现有值替换为拆分值。

Dim k As Integer
Dim lRow as Long
Dim startZip_col As Long
Dim startZip_str As String
Dim startZip_result() As String
Dim startZip_decomposed As Variant
For k = 2 To lRow
startZip_str = Cells(k, startZip_col).Value
startZip_result = Split(startZip_str, ":")

For Each startZip_decomposed In startZip_result
Cells(k, startZip_col) = startZip_result(1)
Next
Next k

我想要拆分的值的一个例子是：

abc:1234abc:5678def:3456

尝试debug.print来确定错误的位置，但列值被正确识别，循环看起来很好，不确定哪里出了问题

Answer 1

逻辑：

1. lRow在哪里。startZip_col是否启动？正确定义和初始化变量/对象
2. 完全限定单元格，否则它可能引用的工作表可能不是您认为的工作表。例如ws.Cells(k, startZip_col).Value，其中ws是相关工作表
3. 拆分前，检查是否存在:

代码：

Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Dim i As Long, j As Long

Dim ZipCol As Long
Dim ZipString As String
Dim ZipResult As Variant
'~~> Change this to the relevant sheet
Set ws = Sheet1

'~~> Change this to the releavant column
ZipCol = 1
With ws
'~~> Get the last row in Col A. Change to relevant column
lRow = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 2 To lRow
ZipString = .Cells(i, ZipCol).Value

'~~> Check if the string contains ":"
If InStr(1, ZipString, ":") Then
ZipResult = Split(ZipString, ":")
'.Cells(1, ZipCol) = ZipResult(1)

'~~> For testing
For j = LBound(ZipResult) To UBound(ZipResult)
Debug.Print ZipResult(j)
Next j
End If
Next i
End With
End Sub