Workplan - Sumproduct formula - Heavy calculation issue.

[Godeled]

Hi everybody,

I tried to make a workplan file that can automatically fill two sheets: weekly (cf weekly consolidation screenshot) and monthly (cf monthly consolidated screenshot) plan based on the daily data input in a third sheet (daily, cf daily data inputs) to ease the reporting tasks at work, and facilitate visualisation + extraction of data. (My workfield, humanitarian aid, is particularly reporting heavy)

At first, I used the following formula : SOMMEPROD((Daily!$C$8:$C$250=$D10)*(Daily!$L$4:$SE$4=L$8)*(Daily!$L$1:$SE$1=L$7)*(Daily!$L$8:$SE$250)) applied to all the different week and activities (cf formula screenshot).
But since we have very old and inefficient computers in the context I am working, it took forever to calculate and display the data in the two linked sheets (monthly and weekly)

Hence, to mitigate this I tried to work on a small VBA code that would calculate the result of the sumproduct formula directly when you hit a button, but the following code took a whooping 27 min to complete its calculation on the working computers of the office. (cf the following code)

Sub Macroplan()

    Dim rngAct As Range, rngMon As Range, rngYea As Range, rngVal As Range
    Dim Month As String, Year As String, Activity As Variant
    Dim i As Integer, j As Integer
    Dim myFormula As Variant
   
    myFormula = "SUMPRODUCT((<plageYear>=<Y>)*(<plageMonth>=<M>)*(<plageACT>=<A>)*(<plageValue>))"
   
    With ThisWorkbook.Sheets("Daily")
        Set rngAct = .Range("$C$8:$C$250")
        Set rngYea = .Range("$L$1:$SE$1")
        Set rngMon = .Range("$L$4:$SE$4")
        Set rngVal = .Range("$L$8:$SE$250")
    End With
     
    For i = 1 To 70 '70
        For j = 1 To 243 '243
            With ThisWorkbook.Worksheets("Weekly")
                Year = .Cells(7, 11 + i)
                Month = .Cells(8, 11 + i)
                Activity = Chr(34) & .Cells(9 + j, 4) & Chr(34)
            End With
   
            If Cells(9 + j, 4).Value <> 0 Then
                myFormula = Replace(myFormula, "<Y>", Year)
                myFormula = Replace(myFormula, "<M>", Month)
                myFormula = Replace(myFormula, "<A>", Activity)
                myFormula = Replace(myFormula, "<plageYear>", rngYea.Address(external:=True))
                myFormula = Replace(myFormula, "<plageMonth>", rngMon.Address(external:=True))
                myFormula = Replace(myFormula, "<plageACT>", rngAct.Address(external:=True))
                myFormula = Replace(myFormula, "<plageValue>", rngVal.Address(external:=True))
                Debug.Print myFormula
                Cells(9 + j, 11 + i).Value = Application.Evaluate(myFormula)
                myFormula = "SUMPRODUCT((<plageYear>=<Y>)*(<plageMonth>=<M>)*(<plageACT>=<A>)*(<plageValue>))"
                Debug.Print myFormula
            End If
                           
        Next
    Next

End Sub

As I am a bit out of ideas and that fresh eyes would help (because I might be overcomplicating things) any help would be much appreciated in lightening the file., or using a better approach/methodology .

I apologize in advance if the subject was already treated (i didn't find a directly related topic with a quick search)

Many thanks,

 

[yky]

I always enclose my code with the following code, whether it is needed.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
................
................

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[hernantorres23]

Hi, I could help you, but I think, that we would need check your data structure
If you can, give another example, explaining what data you have, what conditions they meet, what they should add and what result you expect.

Please, try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares