Formula and WB speed

[JoeExcelHelp]

I have the following formula that I added to the same range of cells within 100 sheets.. The formula works fine but its truly slowing down my WB.
Do you guys have another option or different formula?
Thank You
=-SUMPRODUCT((ClassDist!$F$16:$O$16=AD$12)*(ClassDist!$B$18:$B$500=$D$1&"FT"),ClassDist!$F$18:$O$500)

[HansV]

Unfortunately, there is no really better or faster method to perform these calculations which involve a very large number of multiplications and additions.

An alternative might be not to use formulas, but to use code to perform the calculations only when you need the worksheets to be up-to-date. That would still be slow, but you wouldn't experience delays while editing the workbook.
Or set calculation to Manual instead of Automatic, and only recalculate the workbook when needed.

[JoeExcelHelp]

would it be possible to create a code similar to the attached that applies this formula to only sheet names with 3 characters?
Range within each sheet is - G103:BF106 the following formula is just an example

Code: Select all

Sub SumHiringPlan()
    With Worksheets("HiringPlan").Range("D19:AA44")
        .FormulaR1C1 = "=SUMPRODUCT((Formula)"
        .Value = .Value
    End With
End Sub

[HansV]

It could look like this:

Code: Select all

Sub SumAllSheets()
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        If Len(wsh.Name) = 3 Then
            With wsh.Range("G103:BF106")
                .FormulaR1C1 = "=SUMPRODUCT(...)"
                .Value = .Value
            End With
        End If
    Next wsh
End Sub

You have to fill in the ... of course.

[JoeExcelHelp]

I get a run time error 1004 with the formula highlighted.. I should also ad, the sheet ClassDist has a sheet activated macro.. could that influence the code?

Code: Select all

Sub SumAllSheets()
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        If Len(wsh.Name) = 3 Then
            With wsh.Range("G103:BF104")
                .FormulaR1C1 = "=SUMPRODUCT(ClassDist!R18C2:R150C2=RC3)*(ClassDist!R16C6:R16C15=R12C),ClassDist!R18C6:R150C15)"
                .Value = .Value
            End With
        End If
    Next wsh
End Sub

[HansV]

You missed an opening parenthesis:

Code: Select all

                .FormulaR1C1 = "=SUMPRODUCT((ClassDist!R18C2:R150C2=RC3)*(ClassDist!R16C6:R16C15=R12C),ClassDist!R18C6:R150C15)"

[JoeExcelHelp]

Thank You