LinkBack URL
About LinkBacks
I think I have changed the title.
I'm getting Run-time error ‘13’: type mismatch and unsure about solving.
I think I have changed the title.
Last edited by sdingman; 03-21-2020 at 01:56 PM. Reason: To change the title as requested by a moderator.
What error code is generated if any?
If error occurs in the line ws3... line, likely issue is data type mismatch (i.e. trying to subtract string value from numeric or vice versa).
But it will be bit difficult for us to give you specific solution without sample workbook that replicates your issue. I'd recommend uploading sample workbook, using details outlined in the yellow banner at top.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
CK76, Thanks for replying so quickly.
ws3 cell A2 is where I want the difference between two time stamps from ws2, column 9, I. This is why I have formatted ws3, column A to display Seconds.
CK76, you are correct I am getting Run-time error ‘13’: type mismatch.
ws2 column I is a concatenation of a Date column and a Time column. The sampling data is time stamped every 5 seconds. So I expect the result to be a single digit 5 representing seconds, so the source and destination are custom formatted date and time. When I manually enter the formula into the destination cell it works fine. Steve
Concatenated date & time values are classified as string data type. When using worksheet formula, some string values can be coerced into numeric (ex: using double unary, +0, *1 etc).
However, in VBA, left and write side of operator must be similar (i.e. date & integer, long & double etc). You cannot implicitly add string representation with numeric.
Although I'd recommend using Date column + Time column (instead of concatenation).
You can try below and see if it works as well...
Please Login or Register to view this content.
I'm having trouble trying to attach my sample workbook.
See yellow banner
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
This is my attempt to send a sample of my workbook.
CK76, Thanks again. I'm sorry about not getting to the point, but I use this site so infrequently that every time I visit, its like starting all over. I think I did finally manage to send a sampling of my workbook. Now I see that you replied and said:
, I had never read anything like that. I have used the difference between different times in another macro in my excel file to create my "On Period Analysis" sheet, and never got an type mismatch error.Concatenated date & time values are classified as string data type. When using worksheet formula, some string values can be coerced into numeric (ex: using double unary, +0, *1 etc).
You are also saying, if I'm interpreting what you are saying is that, When I manually placed the formula into cell A2 to calculate the 5, that I got, because,. So you mean that Excel will let me make an error when I manually enter a formula into a cell, but not when VBA places a result into a cell.When using worksheet formula, some string values can be coerced into numeric (ex: using double unary, +0, *1 etc)
You also suggested that I try adding,
What is CDbl?StevePlease Login or Register to view this content.
If use use date string - date string in worksheet cell (manually), it will be interpreted as datetime value - datetime value. But in VBA it is not implicitly treated as such.
CDbl, CLng family of functions are used to convert string data type to Double, Long etc.
In your case instead of CDbl, use CDate to convert datetime string into datetime value.
Ex:
Please Login or Register to view this content.
CK76, I tried your suggested line of code and still got the Type Mis-match. So now since that Type conversion didn't work I think I'll pursue the column merge that you had suggested earlier. I'll let you know the result. Steve
Hmm? Worked fine on my end.
See attached.
As for using + method in cell.
Replace & " " & part with + in your concatenate formula.
CK76, Thanks again. Your sugestion:
did the trick. I'll now mark this as solved. SteveIn your case instead of CDbl, use CDate to convert datetime string into datetime value.
@CK76 and sdingman
Administrative Note:
Sorry, but your post does not comply with Rule #6 of our Forum RULES:
Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.
If you are unclear about the request or instruction, then send a private message to them asking for clarification.
All Participants:
Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks