U can use simply use
Range("E7").value = "==SUMPRODUCT((rng={"L","CL"})*(LEFT
(rng2)="B")*(rng3=""))"

or

Range("E7").formula = "==SUMPRODUCT((rng={"L","CL"})*(LEFT
(rng2)="B")*(rng3=""))"

It shoud work if you are only trying to put a formula.


-----Original Message-----
I find myself in the position of having to use code
instead of functions.

My original function in E7 was as follows:

=SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rn g3=""))

I have put the following into the Worksheet Activate
event of the new file
but it doesn't appear to work:

Range("E7").Value =
Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(r ng2)
=""B"")*(rng3=""""))")

Thanks in advance.

Gareth



.