Values and Formulas - Problem - High Importance

The following line of code gives me a #VALUE!
Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
RngP1.Address(External:=True) & "<=" & Cr8.Address & _
"), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
RngP2.Address(External:=True) & "=" & Cr2.Address & "))")

But if I change this from (just the first few char and rest as same)
Cells(R, C).Value = Evaluate("SUMPRODUCT
to
Cells(R, C).Formula = ("=SUMPRODUCT
it puts the formula in the worksheet and I get the desired result.

What is this???
--
Baapi

Just curious...

If you use .address(external:=true) to all the range variables (including CR1,
Cr7, cr8, cr3, cr2) does it help?



Baapi wrote:
>
> The following line of code gives me a #VALUE!
> Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
> RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
> RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
> RngP1.Address(External:=True) & "<=" & Cr8.Address & _
> "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
> RngP2.Address(External:=True) & "=" & Cr2.Address & "))")
>
> But if I change this from (just the first few char and rest as same)
> Cells(R, C).Value = Evaluate("SUMPRODUCT
> to
> Cells(R, C).Formula = ("=SUMPRODUCT
> it puts the formula in the worksheet and I get the desired result.
>
> What is this???
> --
> Baapi

--

Dave Peterson

Baapi
App.evaluate uses array evaluation rules, maybe your formula will not
evaluate like that
try entering into a cell via range(..).formulaarray = ... If this fails then
the formula probably does not calc as an array

cheers
Simon
"Baapi" <[email protected]> wrote in message
news:[email protected]...
> The following line of code gives me a #VALUE!
> Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
> RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
> RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
> RngP1.Address(External:=True) & "<=" & Cr8.Address & _
> "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
> RngP2.Address(External:=True) & "=" & Cr2.Address & "))")
>
> But if I change this from (just the first few char and rest as same)
> Cells(R, C).Value = Evaluate("SUMPRODUCT
> to
> Cells(R, C).Formula = ("=SUMPRODUCT
> it puts the formula in the worksheet and I get the desired result.
>
> What is this???
> --
> Baapi

But I have this line below

Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" &
RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" &
RngP4.Address(External:=True) & "=" & Cr2.Address & "))")

Working in another part of my code.
--
Baapi


"Dave Peterson" wrote:

> Just curious...
>
> If you use .address(external:=true) to all the range variables (including CR1,
> Cr7, cr8, cr3, cr2) does it help?
>
>
>
> Baapi wrote:
> >
> > The following line of code gives me a #VALUE!
> > Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
> > RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
> > RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
> > RngP1.Address(External:=True) & "<=" & Cr8.Address & _
> > "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
> > RngP2.Address(External:=True) & "=" & Cr2.Address & "))")
> >
> > But if I change this from (just the first few char and rest as same)
> > Cells(R, C).Value = Evaluate("SUMPRODUCT
> > to
> > Cells(R, C).Formula = ("=SUMPRODUCT
> > it puts the formula in the worksheet and I get the desired result.
> >
> > What is this???
> > --
> > Baapi
>
> --
>
> Dave Peterson
>

Maybe you're activating different worksheets in your code.

What happens when you added (external:=true) to those other variables (and now,
in every spot!).

Baapi wrote:
>
> But I have this line below
>
> Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" &
> RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" &
> RngP4.Address(External:=True) & "=" & Cr2.Address & "))")
>
> Working in another part of my code.
> --
> Baapi
>
> "Dave Peterson" wrote:
>
> > Just curious...
> >
> > If you use .address(external:=true) to all the range variables (including CR1,
> > Cr7, cr8, cr3, cr2) does it help?
> >
> >
> >
> > Baapi wrote:
> > >
> > > The following line of code gives me a #VALUE!
> > > Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
> > > RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
> > > RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
> > > RngP1.Address(External:=True) & "<=" & Cr8.Address & _
> > > "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
> > > RngP2.Address(External:=True) & "=" & Cr2.Address & "))")
> > >
> > > But if I change this from (just the first few char and rest as same)
> > > Cells(R, C).Value = Evaluate("SUMPRODUCT
> > > to
> > > Cells(R, C).Formula = ("=SUMPRODUCT
> > > it puts the formula in the worksheet and I get the desired result.
> > >
> > > What is this???
> > > --
> > > Baapi
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I didn't try as I had to deliver this module. But, I deleted all the
(External:= True) and added absolute sheet references and it worked.
But surely at some point I want to change this back to External:= True.
--
Baapi


"Dave Peterson" wrote:

> Maybe you're activating different worksheets in your code.
>
> What happens when you added (external:=true) to those other variables (and now,
> in every spot!).
>
> Baapi wrote:
> >
> > But I have this line below
> >
> > Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" &
> > RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" &
> > RngP4.Address(External:=True) & "=" & Cr2.Address & "))")
> >
> > Working in another part of my code.
> > --
> > Baapi
> >
> > "Dave Peterson" wrote:
> >
> > > Just curious...
> > >
> > > If you use .address(external:=true) to all the range variables (including CR1,
> > > Cr7, cr8, cr3, cr2) does it help?
> > >
> > >
> > >
> > > Baapi wrote:
> > > >
> > > > The following line of code gives me a #VALUE!
> > > > Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" &
> > > > RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" &
> > > > RngP1.Address(External:=True) & ">=" & Cr7.Address & "), --(" &
> > > > RngP1.Address(External:=True) & "<=" & Cr8.Address & _
> > > > "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" &
> > > > RngP2.Address(External:=True) & "=" & Cr2.Address & "))")
> > > >
> > > > But if I change this from (just the first few char and rest as same)
> > > > Cells(R, C).Value = Evaluate("SUMPRODUCT
> > > > to
> > > > Cells(R, C).Formula = ("=SUMPRODUCT
> > > > it puts the formula in the worksheet and I get the desired result.
> > > >
> > > > What is this???
> > > > --
> > > > Baapi
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>