时间戳

首页 > 解决方案 > 通过更改范围内的值，刷新总和值在 Excel 中通过 VBA 宏获取

excel - 通过更改范围内的值，刷新总和值在 Excel 中通过 VBA 宏获取

问题描述

我使用此代码在 VBA 中计算总和。代码正在运行，但是当我更改 Range 中的值时，我如何安排计算总和将被更新。

这是代码：

Sub RangeSum()

Dim rg_1 As Range
Dim rg_2 As Range
Dim order As Range
Dim Bestand As Range
  Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row - 6
    End With
    
    Set rg_1 = Range("W6:W" & LastRow)
    Set rg_2 = Range("X6:X" & LastRow)
    Set order = Range("W4")
    Set Bestand = Range("X4")
    order = Application.WorksheetFunction.Sum(rg_1)
    Bestand = Application.WorksheetFunction.Sum(rg_2)
 
    
End Sub

标签： excelvbasum

解决方案

经过 SJR（感谢 SRJ）的好评后，我发现这个解决方案对我有用：

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Or_Menge As Range
Dim rg_1 As Range
Dim rg_2 As Range
Dim order As Range
Dim Bestand As Range
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row - 6
    End With
    
    Set Or_Menge = Intersect(Target, Range("T6:T" & LastRow))
    
    If Or_Menge Is Nothing Then
    Exit Sub
    
    Else
    
    Set rg_1 = Range("W6:W" & LastRow)
    Set rg_2 = Range("X6:X" & LastRow)
    Set order = Range("W4")
    Set Bestand = Range("X4")
    order = Application.WorksheetFunction.Sum(rg_1)
    Bestand = Application.WorksheetFunction.Sum(rg_2)
    
    End If
    

End Sub

推荐阅读