Help to DEBUG this line of code. Type mismatch error

  • Thread starter Thread starter Ayo
  • Start date Start date
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 &
"<>" & "") & ")"
 
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.
 
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
 
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"
 
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
 
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
 
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
Click to expand...
Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins
 
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
 
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
 
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 & "))")
 
Back
Top