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?

 

[VoG]

Perhaps like this

With Range("A1")
    .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))))"
    .Value = .Value
End With

 

[mucah!t]

Thank you for your suggestion VoG.

What I forgot to mention is that it concerns a ArrayFormula.

I changed the .Formula part of your code into .FormulaArray
but it results in the following error:
"Unable to set the FormulaArray property of the range class"

 

[mucah!t]

Has anyone a solution for this?

 

[mucah!t]

As it seems there is no solution for this error, can anyone tell me why this error occurs?

 

[Jonmo1]

change
.Formula
to
.FormulaArray

 

[mucah!t]

Hello Jonmo,

It's after I changed it to .FormulaArray the error occured?

"Unable to set the FormulaArray property of the range class"

 

[mucah!t]

I also tried the following, a clever solution from Andrew Poulsom to deal with lengthy formulas, but the error insists.

It seems the length isn't causing the error, but I can't figure out what it is

Application.ReferenceStyle = xlR1C1
    With Range("AI2")
        .FormulaArray = "=SUM(IF((WERKBLAD!R[4]C[-33]:R[998]C[-33]>=RC[-5])*(WERKBLAD!R[4]C[-33]:R[998]C[-33]<=RC[-4])*(WERKBLAD!R[2]C[-31]:R[2]C[221]=RC[-3]),IF(WERKBLAD!R[4]C[-31]:R[998]C[221]=""C"",8," & "TheRest)"
        .Replace "TheRest)", "IF(ISNUMBER(SUBSTITUTE(WERKBLAD!R[4]C[-31]:R[998]C[221],""C"",0)+0),SUBSTITUTE(WERKBLAD!R[4]C[-31]:R[998]C[221],""C"",0)+0))))"
    End With
    Application.ReferenceStyle = xlA1

 

[VoG]

No, neither can I. I can insert the formula manually (it takes several seconds to calculate). Inserting with VBA produces an error.

Can you explain what the formula is meant to do and maybe we can find a completely VBA solution.

 

[mucah!t]

Here you find a sample of what I am working on.
The formula is shown in cell K6.
It counts an 8 for each "C" that is found and adds the numbers, following a "C" to it.
it ignores other codes like "CD" etc.
This all based on the criteria given in range J4:L4