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?

 

[Andrew Poulsom]

It works if you use R1C1 reference style:

Range("A1").FormulaArray = "=SUM(IF((WERKBLAD!R6C2:R1000C2>=R2C30)*(WERKBLAD!R6C2:R1000C2<=R2C31)*(WERKBLAD!R4C4:R4C256=R2C32),IF(WERKBLAD!R6C4:R1000C256=""C"",8,IF(ISNUMBER(SUBSTITUTE(WERKBLAD!R6C4:R1000C256,""C"",0)+0),SUBSTITUTE(WERKBLAD!R6C4:R1000C256,""C"",0)+0))))"

That uses absolute references but I expect it can be adjusted for relative references.

 

[VoG]

Can you describe the problem in words or provide a link to the thread where Aladin proposed the formula.

 

[mucah!t]

Can you describe the problem in words or provide a link to the thread where Aladin proposed the formula.

This is the link

 

[mucah!t]

It works if you use R1C1 reference style:

Range("A1").FormulaArray = "=SUM(IF((WERKBLAD!R6C2:R1000C2>=R2C30)*(WERKBLAD!R6C2:R1000C2<=R2C31)*(WERKBLAD!R4C4:R4C256=R2C32),IF(WERKBLAD!R6C4:R1000C256=""C"",8,IF(ISNUMBER(SUBSTITUTE(WERKBLAD!R6C4:R1000C256,""C"",0)+0),SUBSTITUTE(WERKBLAD!R6C4:R1000C256,""C"",0)+0))))"

That uses absolute references but I expect it can be adjusted for relative references.

What the ...
I'm so sure to have tried that at least 3 times or so but didn't work, and now it does
Thank you so much Andrew and VoG!

 

[mucah!t]

Hello again,

I still got one arrayformula left that keeps on giving the error, even with the solution provided by Andrew.

the error is: " unable to set the FormulaArray property of the range class"
What is causing this error and why does the solution provided by Andrew not work for this one and is there a solution?

Range("AG2").FormulaArray = "=SUM(IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],IF(WERKBLAD!R[4]C[-29]:R[998]C[223]=""V"",1)))))+SUM(IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],(WERKBLAD!R[4]C[-29]:R[998]C[223])))))/8"

 

[VoG]

That exceeds the 255 character limit.

This may help http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

 

[mucah!t]

That exceeds the 255 character limit.

This may help http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

I tried that but the error insists on the following line
.FormulaArray = theFormulaPart1

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
 
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],IF(WERKBLAD!R[4]C[-29]:R[998]C[223]=""V"",1)))))+" & _
                          "X_X_X())"
 
    theFormulaPart2 = "SUM(IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],(WERKBLAD!R[4]C[-29]:R[998]C[223])))))/8"
 
    With ActiveSheet.Range("AG2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X())", theFormulaPart2
        '.NumberFormat = "mmm dd"
    End With

 

[Andrew Poulsom]

TheFormulaPart1 is syntactically incorrect. Try:

    Application.ScreenUpdating = False
 
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=IF(WERKBLAD!R[2]C[223]:R[2]C[227]=RC[255],IF(WERKBLAD!R[4]C[225]:R[998]C[225]>=RC[253],IF(WERKBLAD!R[4]C[225]:R[998]C[225]<=RC[254],IF(WERKBLAD!R[4]C[223]:R[998]C[227]=""V"",1))))+" & _
                          "X_X_X()"
 
    theFormulaPart2 = "SUM(IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],(WERKBLAD!R[4]C[-29]:R[998]C[223])))))/8"
 
    With ActiveSheet.Range("AG2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X()", theFormulaPart2
        '.NumberFormat = "mmm dd"
    End With

 

[mucah!t]

We're getting close. your moderation of the code results in the following:

=IF(WERKBLAD!D4:IV4=AF2,IF(WERKBLAD!B6:B1000>=AD2,IF(WERKBLAD!B6:B1000<=AE2,IF(WERKBLAD!D6:IV1000="V",1))))+X_X_X()

Although no error, the X_X_X() part isn't replaced by the socond.

 

[Andrew Poulsom]

The reference style problem we had before:

    Application.ScreenUpdating = False
    Application.ReferenceStyle = xlR1C1
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=IF(WERKBLAD!R[2]C[223]:R[2]C[227]=RC[255],IF(WERKBLAD!R[4]C[225]:R[998]C[225]>=RC[253],IF(WERKBLAD!R[4]C[225]:R[998]C[225]<=RC[254],IF(WERKBLAD!R[4]C[223]:R[998]C[227]=""V"",1))))+" & _
                          "X_X_X()"
 
    theFormulaPart2 = "SUM(IF(WERKBLAD!R[2]C[-29]:R[2]C[223]=RC[-1],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]>=RC[-3],IF(WERKBLAD!R[4]C[-31]:R[998]C[-31]<=RC[-2],(WERKBLAD!R[4]C[-29]:R[998]C[223])))))/8"
 
    With ActiveSheet.Range("AG2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X()", theFormulaPart2
        '.NumberFormat = "mmm dd"
    End With
    Application.ReferenceStyle = xlA1