Run-time error 13: Type mismatch

[Ayo]

Can someone tell me why I am getting the above error in this function. It
only happens at a particular point in my code. It works fine until it get to
that point. And I can't figure out what the mismatch is.

Function msProjected(msDate As String, msStatus As String, currRow As
Integer, MktRow As Integer, startRow As Integer, endRow As Integer) As Integer
msProjected = Evaluate("=IF(AND(D" & currRow & "<>0,D" & currRow &
"<>"""")," & _
"SUMPRODUCT(--('BO Download'!$B$" & startRow & ":$B$" & endRow &
"=B" & MktRow & ")," & _
"--('BO Download'!$F$" & startRow & ":$F$" & endRow & "=C" &
currRow & ")," & _
"--('BO Download'!$H$" & startRow & ":$H$" & endRow &
"=""Selected"")," & _
"--('BO Download'!$" & msDate & "$" & startRow & ":$" & msDate &
"$" & endRow & ">TODAY())," & _
"--('BO Download'!$" & msStatus & "$" & startRow & ":$" &
msStatus & "$" & endRow & "=""P"")),"""")")

End Function

 

[Dave Peterson]

I didn't look very closely, but this looked strange:

"$" & endRow & ">TODAY())," & _

I'd try:

"$" & endRow & ">"& TODAY())," & _

or even using VBA's Date

"$" & endRow & ">" & date & ")," & _

If that doesn't help, then what are the values in those variables passed to the
function?

Be specific to make testing easier.
mktrow = 12, startrow = ...

 

[Barb Reinhardt]

If you're in Excel 2007, you should dimension your Row and Column variables
as Long instead of Integer.

 

[Ayo]

c.Offset(0, 30) = msProjected("Z", "AA", c.Row, marketRow, startRow, endRow)
where :
c.Row=113
marketRow=112
startRow=13802
endRow=20800

The function works great from AF9 to AF112. The problem is when it gets to
AF113, the error pops up. When I was stepping through and I chnaged "Z" to
"V" and "AA" to "W", the program advanced through to the next line. The issue
is not "$" & endRow & ">TODAY())," & _

 

[Ayo]

I don't think that is my problem. My problem is that the function works
great until it get to a particular cell is the worksheet, AF113. Then the
error pops up.

 

[Dave Peterson]

And what's in the cells in row 113 that are used in your formula?

And you haven't shared all the variables yet.

 

[Ayo]

It had to explain what the problem is Dave, because I can't make any sense of
it myself. I would love to send you the file and have you run it yourself,
maybe you can figure it out. I have tried everything I could think of and I
am coming up empty.

 

[Dave Peterson]

Shrink the file down to just the important data and send me a copy.

I use xl2003, so don't save it in xl2007's native format.

 

[Ayo]

Where do I send it. I tried (e-mail address removed) and got this back:

Delivery has failed to these recipients or distribution lists:

(e-mail address removed)
An error occurred while trying to deliver this message to the recipient's
e-mail address. Microsoft Exchange will not try to redeliver this message for
you. Please try resending this message, or provide the following diagnostic
text to your system administrator.

 

[Dave Peterson]

Remove the characters xspam from my email address.

I munge my email address so that spambots don't glean it from newsgroup posting.