如何在VBA中高效为合并单元格区域添加变量值并解决#VALUE错误？

问题出在combined_range.Address返回的是多区域地址（比如$F$21:$AJ$21,$F$193:$AJ$193），直接用Evaluate(combined_range.Address & "+" & CStr(ERV_change))时，Excel无法解析这种多区域的加法表达式，导致返回#VALUE错误。

下面是两种高效的解决方法，都比遍历单个单元格快得多：

方法1：遍历合并区域的每个子区域（Area） #

直接对Union后的每个子区域单独执行Evaluate操作，这样每个子区域都是连续范围，表达式能正常解析：

Sub rangecheck()
    Dim x As Long
    Dim number_of_tenants As Long
    Dim range_names() As String
    Dim ranges() As Range
    Dim dynamic_range As Name
    Dim combined_range As Range
    Dim area As Range
    
    number_of_tenants = 5
    ERV_change = 0.1
    
    ReDim range_names(1 To number_of_tenants)
    ReDim ranges(1 To number_of_tenants)
    
    For x = 1 To number_of_tenants
        range_names(x) = "range" & x
        Set ranges(x) = Range("F" & 21 + 172 * (x - 1) & ":AJ" & 21 + 172 * (x - 1))
        Set dynamic_range = ThisWorkbook.Names.Add(Name:=range_names(x), RefersTo:=ranges(x))
    Next x
    
    Set combined_range = Range("range1")
    For x = 2 To number_of_tenants
        Set current_range = Range("range" & x)
        Set combined_range = Union(combined_range, current_range)
    Next x
    
    ' 遍历每个子区域处理
    For Each area In combined_range.Areas
        area.Value = Evaluate(area.Address & "+" & ERV_change)
    Next area
End Sub

方法2：用数组批量处理（速度更快） #

把每个子区域的值读入数组，完成加法后再写回单元格，减少与工作表的交互次数，效率最高：

Sub rangecheck_Array()
    Dim x As Long
    Dim number_of_tenants As Long
    Dim range_names() As String
    Dim ranges() As Range
    Dim dynamic_range As Name
    Dim combined_range As Range
    Dim area As Range
    Dim arr As Variant
    Dim i As Long, j As Long
    
    number_of_tenants = 5
    ERV_change = 0.1
    
    ReDim range_names(1 To number_of_tenants)
    ReDim ranges(1 To number_of_tenants)
    
    For x = 1 To number_of_tenants
        range_names(x) = "range" & x
        Set ranges(x) = Range("F" & 21 + 172 * (x - 1) & ":AJ" & 21 + 172 * (x - 1))
        Set dynamic_range = ThisWorkbook.Names.Add(Name:=range_names(x), RefersTo:=ranges(x))
    Next x
    
    Set combined_range = Range("range1")
    For x = 2 To number_of_tenants
        Set current_range = Range("range" & x)
        Set combined_range = Union(combined_range, current_range)
    Next x
    
    ' 数组批量处理
    For Each area In combined_range.Areas
        arr = area.Value
        ' 遍历数组元素执行加法
        For i = LBound(arr, 1) To UBound(arr, 1)
            For j = LBound(arr, 2) To UBound(arr, 2)
                ' 跳过空单元格或非数值单元格
                If IsNumeric(arr(i, j)) Then
                    arr(i, j) = arr(i, j) + ERV_change
                End If
            Next j
        Next i
        ' 写回单元格
        area.Value = arr
    Next area
End Sub

说明：

• 方法1的代码改动最小，利用Areas属性拆分多区域，逐个处理连续范围，Evaluate能正常工作。
• 方法2通过数组操作，把数据读入内存后运算，大幅减少工作表读写次数，在数据量大时优势更明显，还能处理非数值单元格的情况。

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