Syntax Error While Recording a Macro

[mucah!t]

Hello all,

Aladin helped me out with the following Formula.

=SUM(IF((WERKBLAD!B6:B1000>=AD2)*(WERKBLAD!B6:B1000<=AE2)*(WERKBLAD!D4:IV4=AF2),IF(WERKBLAD!D6:IV1000="C",8,IF(ISNUMBER(SUBSTITUTE(WERKBLAD!D6:IV1000,"C",0)+0),SUBSTITUTE(WERKBLAD!D6:IV1000,"C",0)+0))))

The problem with it is, that every cell change in my workbook takes now about 15 seconds calculating time.

I thought by putting the formula in a macro and running it only when neccessary to circumvent this problem.

However I can't record the formula, as it keeps on giving a "syntax error in visual basic".

Do you guys know of a solution?

 

[mucah!t]

This is so frustrating...
The error is back on line:
.FormulaArray = theFormulaPart1

 

[mucah!t]

And this doesn't work either.

Application.ScreenUpdating = False
    'Application.ReferenceStyle = xlR1C1
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "==SUM(IF(WERKBLAD!D4:IV4=AF2,IF(WERKBLAD!B6:B1000>=AD2,IF(WERKBLAD!B6:B1000<=AE2,IF(WERKBLAD!D6:IV1000=""V"",1)))))+" & _
                          "X_X_X()"
 
    theFormulaPart2 = "SUM(IF(WERKBLAD!D4:IV4=AF2,IF(WERKBLAD!B6:B1000>=AD2,IF(WERKBLAD!B6:B1000<=AE2,(WERKBLAD!D6:IV1000)))))/8"
 
    With ActiveSheet.Range("AG2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X()", theFormulaPart2
        '.NumberFormat = "mmm dd"
    End With
   ' Application.ReferenceStyle = xlA1

 

[Andrew Poulsom]

The code I posted works for me. Why have you commented out the reference style changes?

 

[mucah!t]

Your code does work Andrew
Thanks!