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")*(rng 3=""))

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(rn g2)=""B"")*(rng3=""""))")

Thanks in advance.

Gareth

From the immediate window:

? Evaluate(
"SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))")
2

With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2
and rng3 are vba object references, then that will not work. In that case,
to fix:

rng.name = "rng"
rng1.name = "rng1"
rng2.name = "rng2"
' then use your formula


--
Regards,
Tom Ogilvy

Gareth wrote in 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")*(rng 3=""))

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(rn g2)=""B"")*(rng3=""""))")

Thanks in advance.

Gareth

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



.

Neither one of those will work. You have to double double-quotes within
strings.

--
Regards,
Tom Ogilvy

"Sharad Nandwani" wrote in message
...
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



.