Help to DEBUG this line of code. Type mismatch error

[Ayo]

'c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$A$" & startRow &
":$A$" & endRow & "<>" & "" & "),--('BO Download'!$D$" & startRow & ":$D$" &
endRow & "<>" & "" & "),--('BO Download'!$E$" & startRow & ":$E$" & endRow &
"<>" & "") & ")"

 

[Rick Rothstein]

I didn't attempt to set this up; but, just from reading it, I think in each
of your comparisons where you have "" that you actually want """" instead
(that will concatenate two quote marks into the text output at those
locations.

 

[Jacob Skaria]

Try the below

c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$A$" & _
startrow & ":$A$" & endrow & "<>""""),--('BO Download'!$D$" & _
startrow & ":$D$" & endrow & "<>""""),--('BO Download'!$E$" & _
startrow & ":$E$" & endrow & "<>""""))")

If this post helps click Yes

 

[Ayo]

Thanks Jacob. But why am I getting "#NAME?" for this line:
c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=" & c.Offset(0, 1) & "),--('BO Download'!$E$"
& startRow & ":$E$" & endRow & "=" & c & "))")
c.Offset(0, 1)="DQE Communications"
c="NLP2"

 

[Jacob Skaria]

The below works for me. You are overwriting the value in c and teh value in
c.Offset(0, 1)...

startrow = 1
endrow = 10
Set c = ActiveCell

c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" & _
startrow & ":$D$" & endrow & "=" & c.Offset(0, 1) & _
"),--('BO Download'!$E$" & startrow & ":$E$" & endrow & "=" & c & "))")

c.Offset(0, 1) = "DQE Communications"
c = "NLP2"

If this post helps click Yes

 

[Ayo]

My problem is that the formular in the Evaluate function is not evaluating
correctly. I tried it another way:
For Each c In Regws.Range("B" & Regws_startRow & ":B" & Regws_endRow).Cells
If c.Row <> Regws_endRow Then
c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "))")
c.Offset(0, 2).Value should be 126 but it is evaluating to 1

These are the values for the variables:
Regws_startRow=4
Regws_endRow=8
startRow=5
endRow=550

 

[Tushar Mehta]

Is there a reason you cannot enter the formula in D47?

Evaluate is not as straightforward as people would like it to be. It's
a method, not a function. When used w/o a qualifier it defaults to
Application.Evaluate(...) and then the unqualified references to the
comparison cells B{n} and C{n} refer to...well, I don't know.

You could try using the {worksheet}.Evaluate method but like I wrote
at the top, why not enter the formulas in the cells of interest and
let Excel do the hard work?

My problem is that the formular in the Evaluate function is not evaluating
correctly. I tried it another way:
For Each c In Regws.Range("B" & Regws_startRow & ":B" & Regws_endRow).Cells
If c.Row <> Regws_endRow Then
c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "))")
c.Offset(0, 2).Value should be 126 but it is evaluating to 1

These are the values for the variables:
Regws_startRow=4
Regws_endRow=8
startRow=5
endRow=550

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

 

[Jacob Skaria]

We are going far from where we have started..You are looking for a text 'C4'
and 'B4' in ColD and ColE....respectively....Is that what you want...Try and
enter the formula to the cell...

For Each c In Regws.Range("B" & Regws_startRow & ":B" & Regws_endRow)
If c.Row <> Regws_endRow Then
c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" & _
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" & _
startRow & ":$E$" & endRow & "=B" & c.Row & "))")
End If
Next

If this post helps click Yes

 

[Ayo]

I am trying to recreate the formular"=SUMPRODUCT(--('BO
Download'!$D$5:$D$550=C4),--('BO Download'!$E$5:$E$550=B4))" in the Evaluate
function of the code

 

[Dave Peterson]

If you were recreating that exact formula (C4 and B4):

c.Offset(0, 2).Value _
= regws.Evaluate("SUMPRODUCT(--('BO Download'!$D$5:$D$550=C4)," _
& "--('BO Download'!$E$5:$E$550=B4))")

or using the same row as the row that c is in:

c.Offset(0, 2).Value = regws.Evaluate _
("SUMPRODUCT(--('BO Download'!$D$5:$D$550=C" & c.Row & ")," _
& "--('BO Download'!$E$5:$E$550=B" & c.Row & "))")