I am trying to make this comparison work;
xlApp.Range("j15") =
xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))
'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria match. If I
plug it into the cell manually with out the Worksheets. etc syntax and use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong

Not tested as I was not sure what you were doing in the middle with the
comment mark, but this should give you an idea

With xlApp
.WorksheetFunction.SumProduct ((.Worksheets(ShtRef).Range(ColRng) =
"89298") * (.Worksheets(ShtRef).Range(rngref) 0)) + _
.WorksheetFunction.CountIf(.Worksheets("Dec
Contract").Range(ColRng), "89298")
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I am trying to make this comparison work;
xlApp.Range("j15") =

xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))
'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria match. If
I
plug it into the cell manually with out the Worksheets. etc syntax and use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong

Bob, I tried your suggestion but still no results. I was using the ",--" to
create a countif situation. Anyway I do Not get any errors but I also get no
data. My ranges show up for when I do a mouse over, so I'm a little stumped



"Bob Phillips" wrote:

Not tested as I was not sure what you were doing in the middle with the
comment mark, but this should give you an idea

With xlApp
.WorksheetFunction.SumProduct ((.Worksheets(ShtRef).Range(ColRng) =
"89298") * (.Worksheets(ShtRef).Range(rngref) 0)) + _
.WorksheetFunction.CountIf(.Worksheets("Dec
Contract").Range(ColRng), "89298")
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I am trying to make this comparison work;
xlApp.Range("j15") =

xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))
'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria match. If
I
plug it into the cell manually with out the Worksheets. etc syntax and use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong

Hi
using this kind o Sumproduct syntax is not possible within VBA. In VBA you
can only use the 'classic' Sumproduct syntax: If you need to use SP to
conditional count/sum you have to use Evaluate within VBA

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I am trying to make this comparison work;
xlApp.Range("j15") =
xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))
'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria match. If
I
plug it into the cell manually with out the Worksheets. etc syntax and use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong

The -- is an Excel thing, not VBA, that is why I took it out. If you get no
error, but no data, check the variables, like colRng. Also, are they text
fields or numeric, as you used "89298", maybe try 89298.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
Bob, I tried your suggestion but still no results. I was using the ",--"
to
create a countif situation. Anyway I do Not get any errors but I also get
no
data. My ranges show up for when I do a mouse over, so I'm a little
stumped



"Bob Phillips" wrote:

Not tested as I was not sure what you were doing in the middle with the
comment mark, but this should give you an idea

With xlApp
.WorksheetFunction.SumProduct
((.Worksheets(ShtRef).Range(ColRng) =
"89298") * (.Worksheets(ShtRef).Range(rngref) 0)) + _
.WorksheetFunction.CountIf(.Worksheets("Dec
Contract").Range(ColRng), "89298")
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I am trying to make this comparison work;
xlApp.Range("j15") =


xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))

'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria
match. If
I
plug it into the cell manually with out the Worksheets. etc syntax and
use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong

oops, forgot that.

You could give this a try (I have tested it as best I could)

With Application 'xlApp
Set rng1 = .Worksheets(shtref).Range(colrng)
Set rng2 = .Worksheets(shtref).Range(rngref)
.Range("j15") = Evaluate("SumProduct((" & rng1.Address &
"=""89298"")*" & _
"(" & rng2.Address & "0))") + _
.WorksheetFunction.CountIf(.Worksheets("Dec
Contract").Range(colrng), "89298")
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Frank Kabel" wrote in message
...
Hi
using this kind o Sumproduct syntax is not possible within VBA. In VBA you
can only use the 'classic' Sumproduct syntax: If you need to use SP to
conditional count/sum you have to use Evaluate within VBA

--
Regards
Frank Kabel
Frankfurt, Germany
"Jeff" schrieb im Newsbeitrag
...
I am trying to make this comparison work;
xlApp.Range("j15") =

xlApp.WorksheetFunction.SumProduct(--(xlApp.Worksheets(ShtRef).Range(ColRng)
= "89298"), --(xlApp.Worksheets(ShtRef).Range(rngref) 0))
'xlApp.WorksheetFunction.CountIf(xlApp.Worksheets( ShtRef).Range(ColRng),
"89298") + xlApp.WorksheetFunction.CountIf(xlApp.Worksheets(" Dec
Contract").Range(ColRng), "89298")

Variables for range and sheet names are correct and work in many other
situations so I know its not them.

I want to set a field equal to the number of times both criteria match.
If
I
plug it into the cell manually with out the Worksheets. etc syntax and
use
the Range:Range notation it works on the range.

Any Ideas on what I'm doing wrong