| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to DEBUG this line of code. Type mismatch error
'c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$A$" & startRow &
":$A$" & endRow & "<" & "" & "),--('BO Download'!$D$" & startRow & ":$D$" & endRow & "<" & "" & "),--('BO Download'!$E$" & startRow & ":$E$" & endRow & "<" & "") & ")" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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. -- Rick (MVP - Excel) "Ayo" wrote in message ... 'c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$A$" & startRow & ":$A$" & endRow & "<" & "" & "),--('BO Download'!$D$" & startRow & ":$D$" & endRow & "<" & "" & "),--('BO Download'!$E$" & startRow & ":$E$" & endRow & "<" & "") & ")" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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? On Tue, 20 Oct 2009 11:19:02 -0700, 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 & "<" & "") & ")" Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help to DEBUG this line of code. Type mismatch error
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 & "<" & "") & ")" |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting r/t 13 - Type Mismatch on this line - Why? | Excel Programming | |||
| Type Mismatch error in VBA code | Excel Programming | |||
| Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
| [Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) | |||
Linear Mode
