|
Run-time error 13: Type mismatch
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 |
Run-time error 13: Type mismatch
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 = ... Ayo wrote: 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 |
Run-time error 13: Type mismatch
If you're in Excel 2007, you should dimension your Row and Column variables
as Long instead of Integer. -- HTH, Barb Reinhardt "Ayo" wrote: 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 |
Run-time error 13: Type mismatch
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())," & _ "Dave Peterson" wrote: 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 = ... Ayo wrote: 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 . |
Run-time error 13: Type mismatch
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. "Barb Reinhardt" wrote: If you're in Excel 2007, you should dimension your Row and Column variables as Long instead of Integer. -- HTH, Barb Reinhardt "Ayo" wrote: 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 |
Run-time error 13: Type mismatch
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 wrote: 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())," & _ "Dave Peterson" wrote: 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 = ... Ayo wrote: 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 . -- Dave Peterson |
Run-time error 13: Type mismatch
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" wrote: 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 wrote: 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())," & _ "Dave Peterson" wrote: 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 = ... Ayo wrote: 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 . -- Dave Peterson . |
Run-time error 13: Type mismatch
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 wrote: 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" wrote: 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 wrote: 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())," & _ "Dave Peterson" wrote: 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 = ... Ayo wrote: 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 . -- Dave Peterson . -- Dave Peterson |
Run-time error 13: Type mismatch
Remove the characters xspam from my email address.
I munge my email address so that spambots don't glean it from newsgroup posting. Ayo wrote: Where do I send it. I tried and got this back: Delivery has failed to these recipients or distribution lists: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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())," & _ "Dave Peterson" wrote: 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 = ... Ayo wrote: 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 . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| All times are GMT +1. The time now is 09:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com