VBA基于源数组更新目标数组时sa2数组为空问题排查

正在编写VBA子过程，预期实现效果：用户将食谱内容复制粘贴至单元格后，文本内的过敏原词汇自动转换为全大写格式，例如将“Milk”转换为“MILK”。出于学习目的，不使用查找/替换方案，要求通过数组操作实现需求。
当前代码运行存在异常：sa2数组仅存储空值，无法输出预期结果。

Sub Allergen()

Dim nr As Integer
Dim r As Range
Dim sa As Variant
Dim sa2 As Variant
Dim i As Integer


nr = WorksheetFunction.CountA(Columns("E:E"))
sa = Split(Sheets("Sheet1").Range("E" & nr).Offset(-1, 0))
sa2 = Split(Sheets("Sheet1").Range("E" & nr).Offset(-1, 0))

ReDim Preserve sa2(0 To UBound(sa))

For i = LBound(sa) To UBound(sa)

If sa(i) = "Milk" Then
sa2(i) = "MILK"
End If

• 行号获取逻辑错误：WorksheetFunction.CountA(Columns("E:E"))返回的是E列非空单元格的数量，不是最后一个非空单元格的行号，当E列非空单元格不连续时，会定位到空单元格，拆分空字符串得到的数组自然全是空值。
• 多余的重定义数组操作：sa2已经通过Split赋值为和sa长度一致的数组，后续执行ReDim Preserve会重置数组存储的内容，是sa2出现空值的核心诱因。
• 未显式读取单元格值：直接将单元格对象传入Split函数，依赖隐式类型转换读取文本，兼容性差容易出现取值异常。
• 逻辑缺失：循环结束后没有将处理完成的数组重新拼接为字符串写回单元格，即使替换成功也不会展示结果。
• 匹配逻辑鲁棒性差：仅支持精确匹配"Milk"字符串，单词带标点、存在大小写变体时会匹配失败。
• 变量类型风险：用Integer存储行号，当行号超过32767时会触发溢出报错。

Sub Allergen()
    Dim nr As Long
    Dim targetRange As Range
    Dim targetText As String
    Dim sa As Variant
    Dim sa2 As Variant
    Dim i As Long
    ' 可自行扩展过敏原词库
    Dim allergenList As Variant
    allergenList = Array("Milk", "Egg", "Peanut", "Soy", "Wheat", "Tree Nut", "Shellfish")
    
    ' 正确定位E列最后一个非空单元格（即用户最新粘贴内容的单元格）
    ' 如果需要定位倒数第二个非空单元格，在End(xlUp)后加.Offset(-1, 0)即可
    Set targetRange = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "E").End(xlUp)
    targetText = targetRange.Value
    
    ' 按空格拆分文本为单词数组
    sa = Split(targetText, " ")
    ' 直接复制原始数组到sa2，避免重定义清空内容
    sa2 = sa
    
    ' 遍历单词做替换
    For i = LBound(sa) To UBound(sa)
        Dim allergen As Variant
        For Each allergen In allergenList
            ' 清除单词前后常见标点，避免带逗号、括号、句号时匹配失败
            Dim cleanWord As String
            cleanWord = Trim(Replace(Replace(Replace(Replace(sa(i), ",", ""), ".", ""), "(", ""), ")", ""))
            ' 忽略大小写匹配过敏原
            If StrComp(cleanWord, allergen, vbTextCompare) = 0 Then
                ' 保留原单词附带的标点，仅将过敏原词汇本身转为全大写
                sa2(i) = Replace(sa(i), cleanWord, UCase(allergen), Compare:=vbTextCompare)
                Exit For
            End If
        Next
    Next i
    
    ' 将处理后的数组合并为完整文本，写回原单元格
    targetRange.Value = Join(sa2, " ")
End Sub

• 用Cells(行号,列号).End(xlUp)方法获取E列最后一个非空单元格位置，替代原有的CountA计数逻辑，定位准确不会偏移到空单元格。
• 移除多余的ReDim Preserve语句，直接通过数组赋值复制拆分后的原始内容，从根源避免数组内容被清空。
• 显式读取单元格的Value属性获取待处理文本，消除隐式类型转换的兼容性问题。
• 新增过敏原词库配置，支持批量扩展需要替换的词汇，替换时自动忽略大小写、过滤单词前后标点，匹配准确率更高。
• 补充数组拼接回写逻辑，通过Join函数将处理完的数组合并为完整字符串写回单元格，完成替换流程。
• 所有行号、循环计数变量统一使用Long类型，避免大行数场景下的溢出报错。

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