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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>