Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help to DEBUG this line of code. Type mismatch error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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
&
"<" & "") & ")"


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 126
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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 &
"<" & "") & ")"

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting r/t 13 - Type Mismatch on this line - Why? JMay Excel Programming 2 November 25th 08 12:30 PM
Type Mismatch error in VBA code bobby Excel Programming 3 November 10th 05 08:05 AM
Help: Compile error: type mismatch: array or user defined type expected lvcha.gouqizi Excel Programming 1 October 31st 05 08:20 PM
[Q] Save As throws type mismatch error in control's code? Jason Weiss Excel Discussion (Misc queries) 1 July 16th 05 04:21 AM


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement