PRoblem with Ranges and Sumproduct under VBA
 

Have I lost my mind!!! I am trying to count unique occurances using
Sumproduct. I have the sheet defined as well as the ranges. The Ranges are
Dimas Range and the Sheetname is Dim as String. This setup works elsewhere in
my code except that I reference an outside sheet. Here is a snippit of what
works;

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef3 & " =""TBD""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef2 & "<""CLS""))")

Here is the code I'm trying to get to run but all I get is a #value error
although the ranges are identical in size, and I'm no trying to reference an
outside sheet

xlApp.Range("I10") = xlApp.Evaluate("=SUMPRODUCT(--(" & _
ShtRef & "'!" & xlRng7.Address & _
"=""Hours""),--(" & _
ShtRef & "'!" & xlRng8.Address & _
"=""100""))")

Does anyone know of a place where the sumproduct feature is reaslly well
documented, ie book, website ect that might help me figure out my own
problems.

PRoblem with Ranges and Sumproduct under VBA
 

Seems you forgot the " ' " (without quote marks) before the ShtRef ?

cut and paste below and try :-

xlApp.Range("I10") = xlApp.Evaluate("=SUMPRODUCT(--('" & _
ShtRef & "'!" & xlRng7.Address & _
"=""Hours""),--('" & _
ShtRef & "'!" & xlRng8.Address & _
"=""100""))")

What I have done?: - Andded a ' just after the last bracket in line 1
and line 3.

Try this.

Sharad


"Jeff" wrote in message
...
Have I lost my mind!!! I am trying to count unique occurances using
Sumproduct. I have the sheet defined as well as the ranges. The Ranges are
Dimas Range and the Sheetname is Dim as String. This setup works elsewhere
in
my code except that I reference an outside sheet. Here is a snippit of
what
works;

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef3 & " =""TBD""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef2 & "<""CLS""))")

Here is the code I'm trying to get to run but all I get is a #value error
although the ranges are identical in size, and I'm no trying to reference
an
outside sheet

xlApp.Range("I10") = xlApp.Evaluate("=SUMPRODUCT(--(" & _
ShtRef & "'!" & xlRng7.Address & _
"=""Hours""),--(" & _
ShtRef & "'!" & xlRng8.Address & _
"=""100""))")

Does anyone know of a place where the sumproduct feature is reaslly well
documented, ie book, website ect that might help me figure out my own
problems.

PRoblem with Ranges and Sumproduct under VBA
 

Thanks for the quick response. I am having problems still though. I can get
some things to add/count and others refuues. I am using:
xlApp.Range("j10") = xlApp.Evaluate("=SUMPRODUCT(--('" & _
ShtRef & "'!" & xlRng10.Address & """0""))")
Where I want to count up rows and then verify them against other criteria.
However I can't get this to count the cells that are not empty/zero.

What have I done wrong here?


"Sharad Naik" wrote:

Seems you forgot the " ' " (without quote marks) before the ShtRef ?

cut and paste below and try :-

xlApp.Range("I10") = xlApp.Evaluate("=SUMPRODUCT(--('" & _
ShtRef & "'!" & xlRng7.Address & _
"=""Hours""),--('" & _
ShtRef & "'!" & xlRng8.Address & _
"=""100""))")

What I have done?: - Andded a ' just after the last bracket in line 1
and line 3.

Try this.

Sharad


"Jeff" wrote in message
...
Have I lost my mind!!! I am trying to count unique occurances using
Sumproduct. I have the sheet defined as well as the ranges. The Ranges are
Dimas Range and the Sheetname is Dim as String. This setup works elsewhere
in
my code except that I reference an outside sheet. Here is a snippit of
what
works;

xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef3 & " =""TBD""),--('[Release Plan (1,2,3,4).xls]" & ShtRef &
"'!" & CntRef2 & "<""CLS""))")

Here is the code I'm trying to get to run but all I get is a #value error
although the ranges are identical in size, and I'm no trying to reference
an
outside sheet

xlApp.Range("I10") = xlApp.Evaluate("=SUMPRODUCT(--(" & _
ShtRef & "'!" & xlRng7.Address & _
"=""Hours""),--(" & _
ShtRef & "'!" & xlRng8.Address & _
"=""100""))")

Does anyone know of a place where the sumproduct feature is reaslly well
documented, ie book, website ect that might help me figure out my own
problems.