Values and Formulas - Problem - High Importance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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???
 
Just curious...

If you use .address(external:=true) to all the range variables (including CR1,
Cr7, cr8, cr3, cr2) does it help?
 
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
 
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.
 
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!).
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.
Click to expand...
 
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.
 
Back
Top