我有以下問題： cellA12=subtotal, cell c12= 23 , cell A40= subtotal, cell c40= 12 , cell A60 = subtotal, cell C60= 50. Cell B80 = 總計, cell c80= C12-C40 c60
但是每個月，單元格 A 的位置都包含“小計”，單元格包含數字，單元格 B 每月都在變化。我是否可以使用公式或 VBA 腳本使其成為動態的第一個小計減去第二個小計并每月添加第三個小計？
問候，喬
uj5u.com熱心網友回復：
請嘗試下一個功能：
Function SubtotalOperations(rng As Range) As Double
Dim FindSubtotal As Range, firstOne As String, subTCount As Long
Dim subTCalculate As Double, firstSubt As Double, secondSubt As Double
With rng
Set FindSubtotal = .Find("SUBTOTAL", After:=.cells(1), LookIn:=xlFormulas, lookat:=xlPart) 'first subtotal found
If Not FindSubtotal Is Nothing Then
firstOne = FindSubtotal.Address 'cell address of the first subtotal
Do
subTCount = subTCount 1 'count the found subtotals
If subTCount = 1 Then
firstSubt = FindSubtotal.Value
ElseIf subTCount = 2 Then
secondSubt = FindSubtotal.Value - firstSubt
ElseIf subTCount = 3 Then
SubtotalOperations = FindSubtotal.Value secondSubt: Exit Function
End If
Set FindSubtotal = .FindNext(FindSubtotal)
Loop While FindSubtotal.Address <> firstOne 'loop until reaching first found subtotal cell address
End If
End With
End Function
可以通過以下方式進行測驗：
Sub testSubtOperations()
Dim sh As Worksheet
Set sh = ActiveSheet
MsgBox SubtotalOperations(sh.Range("A:A"))
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/512656.html