Help to DEBUG this line of code. Type mismatch error
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 & "))")
Ayo wrote:
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
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Ayo" wrote:
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
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Ayo" wrote:
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" wrote:
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
---------------
Jacob Skaria
"Ayo" wrote:
'c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$A$" & startRow &
":$A$" & endRow & "<" & "" & "),--('BO Download'!$D$" & startRow & ":$D$" &
endRow & "<" & "" & "),--('BO Download'!$E$" & startRow & ":$E$" & endRow &
"<" & "") & ")"
--
Dave Peterson
|