Dynamic calculate the total-CodePudding

I have the following question: cellA12=subtotal, cell c12= 23 , cell A40= subtotal, cell c40= 12 , cell A60 = subtotal, cell C60= 50. Cell B80 = grand total, cell c80= C12-C40 c60

But every month, the location of cell A include “subtotal” and Cell include the number and cell B is change every month. Could I have formula or VBA script to make it dynamic that first subtotal minus second subtotal and add third subtotal every month?

Regards, Joe

CodePudding user response：

Please, try the next function:

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

It can be tested in the next way:

Sub testSubtOperations()
   Dim sh As Worksheet
   
   Set sh = ActiveSheet
   MsgBox SubtotalOperations(sh.Range("A:A"))
End Sub