含数组的VBA循环未按预期运行，请求排查解决

问题描述 #

基于原有可运行代码修改后，仅保留「检查单元格是否以"Completed"开头」的判断逻辑，但运行子程序后无任何反应，也无错误提示。修改后的核心循环代码如下：

For r = 1 To rCount
     rString = CStr(sData(r, 3))
       If InStr(rString, "Completed") = 1 Then    ' 若第r行C列单元格以"Completed"开头
      nIndex = Application.Match(rString, Faelle, 0)
        If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
    End If
Next r

完整子程序代码：

Sub setStatus()
    
    Dim wb          As Workbook: Set wb = ThisWorkbook
    Dim ws          As Worksheet: Set ws = wb.Worksheets(3)
    Dim rg          As Range: Set rg = ws.Range("A1").CurrentRegion
    
    Dim rCount      As Long: rCount = rg.Rows.Count
    Dim srg         As Range: Set srg = rg.Resize(rCount)
    Dim sData()     As Variant: sData = srg.Resize(, 3).Value
    Dim dData()     As Variant: ReDim dData(1 To rCount, 1 To 1)
    Dim nIndex      As Variant, r As Long, rString As String
    
    Dim Faelle()    As Variant: Faelle = VBA.Array("Status1", "Status2", "Status3", "Status4", "Status5")
    Dim Werte()     As Variant: Werte = VBA.Array(1, 2, 3, 4, 5)
    

For r = 1 To rCount
    rString = CStr(sData(r, 3))
    If InStr(rString, "Completed") = 1 Then        ' 若第r行C列单元格以"Completed"开头
    nIndex = Application.Match(rString, Faelle, 0)
    If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
End If
Next r

Dim drg             As Range: Set drg = srg.Columns(6)

drg.Value = dData

End Sub

问题原因 #

1. 匹配逻辑不匹配：Faelle数组存储的是Status1这类状态值，但rString是以"Completed"开头的完整字符串（比如Completed Status1），直接用Match(rString, Faelle, 0)永远找不到匹配项，nIndex始终为错误值，导致dData无法赋值。
2. InStr函数的潜在问题：未指定比较模式，若单元格内容存在大小写差异（比如completed），会导致判断失效；同时参数顺序虽无逻辑问题，但显式指定起始位置会更清晰。

修复方案 #

方案1：提取状态部分后匹配（假设"Completed"与状态值以空格分隔） #

修改循环逻辑，先从rString中提取"Completed"之后的状态内容，再与Faelle数组匹配：

For r = 1 To rCount
    rString = CStr(sData(r, 3))
    ' 不区分大小写检查是否以"Completed"开头
    If InStr(1, rString, "Completed", vbTextCompare) = 1 Then
        ' 提取"Completed"后的状态部分（去除空格）
        Dim statusPart As String
        statusPart = Trim(Mid(rString, Len("Completed") + 1))
        ' 匹配状态部分到Faelle数组
        nIndex = Application.Match(statusPart, Faelle, 0)
        If IsNumeric(nIndex) Then
            dData(r, 1) = Werte(nIndex - 1)
        End If
    End If
Next r

方案2：直接检查Faelle元素是否在rString中（若状态值位置不固定） #

如果"Completed"和状态值的组合形式不固定（比如Completed-Status1），可以遍历Faelle数组检查是否存在于rString中：

For r = 1 To rCount
    rString = CStr(sData(r, 3))
    If InStr(1, rString, "Completed", vbTextCompare) = 1 Then
        ' 遍历Faelle数组查找匹配的状态
        Dim i As Long
        For i = LBound(Faelle) To UBound(Faelle)
            If InStr(1, rString, Faelle(i), vbTextCompare) > 0 Then
                dData(r, 1) = Werte(i)
                Exit For ' 找到匹配后退出内层循环
            End If
        Next i
    End If
Next r

调试建议 #

在循环中添加Debug.Print语句，输出关键变量值，便于排查：

For r = 1 To rCount
    rString = CStr(sData(r, 3))
    Debug.Print "行号：" & r, "单元格内容：" & rString
    If InStr(1, rString, "Completed", vbTextCompare) = 1 Then
        ' ... 其他逻辑 ...
        Debug.Print "匹配索引：" & nIndex
    End If
Next r

打开VBA编辑器的「立即窗口」（快捷键Ctrl+G）即可查看输出。

内容的提问来源于stack exchange，提问作者maliebina