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

Values and Formulas - Problem - High Importance
 

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

Values and Formulas - Problem - High Importance
 

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

Values and Formulas - Problem - High Importance
 

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

Values and Formulas - Problem - High Importance
 

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

Values and Formulas - Problem - High Importance
 

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