PRoblem with Ranges and Sumproduct under VBA

[Jeff]

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.

 

[Sharad Naik]

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]

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?

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

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.