Help to DEBUG this line of code. Type mismatch error

A

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 &
"<>" & "") & ")"
 
R

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.
 
J

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
 
A

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"
 
J

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
 
A

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
 
T

Tushar Mehta

Is there a reason you cannot enter the formula in D4:D7?

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
 
J

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
 
A

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
 
D

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 & "))")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top