excel - 范围不会更改多个单元格

Question

Sub ButtonLower()

Range("b2", "b7").Value = Range("b2", "b7").Value - 2

End Sub
Sub ButtonHigher()

Range("b2", "b7").Value = Range("b2", "b7").Value + 2

End Sub

最终结果是只有 B2的值变化，而不是整个范围。有人可以帮我弄这个吗？

Answer 1

您不能直接对整个数组进行数学运算。使用评估:

With ActiveSheet.Range("B2:B7")
    .Value = ActiveSheet.Evaluate(.Address & " - 2")
End With

和

With ActiveSheet.Range("B2:B7")
    .Value = ActiveSheet.Evaluate(.Address & " + 2")
End With

或者您可以将整个加载到一个变体数组中并循环该数组:

Sub ButtonLower()
    Dim arr() As Variant
    arr = ActiveSheet.Range("b2:b7").Value

    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        Dim j As Long
        For j = LBound(arr, 2) To UBound(arr, 2)
            arr(i, j) = arr(i, j) - 2
        Next j
    Next i

    ActiveSheet.Range("b2:b7").Value = arr
End Sub