Hello Mark,

Thank you for your answer.

I tried your code on my system (PIII @ 600 Mhz., 256MB, win 98 SE) and it tooks nearly 2,85 s. to complete.

Your example clearly shows that my problem is not Excel writing formulas too slow. In fact, after modifying your code to use ranges in a way similar to my faulting subroutine the execution time is good enough.

By now I think that the problem has to do with the complexity of the formula, so here it goes my testbed subroutine which executes in 45 s. on my system.

Sub TestSetFormula2()

Dim t As Single

Dim s As String

Dim r As Range, rng As Range

Set rng = Worksheets(1).Range("b1:b10")

s = Worksheets(2).Range("e13").Formula

' s holds the formula:

' =INDEX(__DATOS_2002_,MATCH(CONCATENATE($A$2,"-",E$4,"-",$A13),DATOS_ID_ExpMesPers,0),MATCH("Cost_ExpMesPers",DATOS_CAMPOS,0))

' where "__DATOS_2002_" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$1:$65536"

' and $A$2 = "P-0002", E$4 = "7" and $A13 = "AS"

' So its concatenation gives as result the string "P-0002-7-AS"

' which is used as the key to match values in the column labeled "DATOS_ID_ExpMesPers"

' "DATOS_ID_ExpMesPers" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$AK:$AK"

' and "DATOS_CAMPOS" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$1:$1"

t = Timer

For Each r In rng

r.Formula = s

Next

MsgBox "Elapsed: " & Timer - t & " s."

End Sub

It seems that after writing the formula in the cell, it has to evaluate it in order to show its value.

What I'm doing wrong?

Do you have a solution?

Is there an alternative way of doing it?

I was wondering if substituting the formula by a wrapper function that simply evals a string, and passing my formula to the function as a parameter will work, but if the formula has named ranges, it returns "#¡VALUE!".

Function MyEval(strExpression As String) As Variant

Application.Volatile

MyEval = Evaluate(strExpression)

End Function

Hope you can help me :wink:

Thanks again and best wishes,

-- Julio Garcia