+ Reply to Thread
Results 1 to 29 of 29

CDbl giving type mismatch error

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    CDbl giving type mismatch error

    Hi, I wrote a macro for someone and in this macro there is a section where i take a string.....strip off special character and spaces and i am left with somethign like this "198.40"

    Basically a string.

    i then use CDbl to convert it to a number.

    Junk1=CDbl("198.40")


    but for some reason i get a type mismatch error at this line...........welll actually my friend gets a type mismatch error at this point ......AND I DO NOT get error.

    ANy ideas?

    Note: i can not publish code with example files as some have private information.

  2. #2
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    dont know if this is important but i am in the USA and my friend is in Poland.

  3. #3
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: CDbl giving type mismatch error

    Without the file, it's almost impossible to guess what's going run.

    Try to include sample data in a workbook, WITHOUT confidential information and attach here.

    Note: As per your second message, it seems that the problem is arising because of the decimal point character used in USA and Poland are different.
    Last edited by Haluk; 07-28-2020 at 07:59 AM.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,548

    Re: CDbl giving type mismatch error

    What version of Excel is your friend using?

    These things are easier to debug if we can see the workbook. Are you not able to remove stuff that's not involved and anonymise anything that's left and relevant.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: CDbl giving type mismatch error

    It may be that US / Poland have different qualifiers for strings, and/or for numbers ( thousand's separator / decimal qualifier ).

    In some countries , the decimal place is a comma, others it's a period/full-stop.

    TL:DR Looks like it's that Poland uses a comma, USA uses a period.

    Google : brilliantmaps.com/decimals/ ( Since I'm not allowed to post links / images etc.. )

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,548

    Re: CDbl giving type mismatch error

    Quote Originally Posted by welchs101 View Post
    dont know if this is important but i am in the USA and my friend is in Poland.
    Have you tried temporarily changing your Windows Regional setting to the same as your friend's and seeing whether you get the same problem. That would confirm whether a regional setting is the problem.

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: CDbl giving type mismatch error

    @welchs101;

    Can you try this:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    thanks all i will look into all of those questions and get back to you.........quick question how to change regional settings that you are referring to ..... i just want to make sure i am changing the right thing

  9. #9
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: CDbl giving type mismatch error

    In Office 2010;

    File >> Options >> Advanced > Editing Options [Uncheck the box "Use system operators"] and change the settings.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    get them to do a simple test, via the immediate window.

    if they try with . or , do they get the same values as below? Well I guess they would actually get the reverse as , is the decimal separator

    Please Login or Register  to view this content.
    ALT+F11 to open VBE
    CTRL+G to open Immediate window
    Paste ?CDbl("198.40"),CDbl("198,40")
    and press enter
    Cheers
    Andy
    www.andypope.info

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    ok, still verifying all the things you mentioned. i did go to settings and they are using "," instead of "." but in my code i accounted/checked for this.

    I did try replacing "CDbl" with "Val" and............IT WORKED!!!!!!!

    But i have NO IDEA WHY?

    Any thoughts?

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    Val may not have raised an error but are you sure you are getting the correct value?

    Please Login or Register  to view this content.
    Note the loss of 40 and the factor reduction 198 instead of 19840.

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    so i did the test you asked for using the immediate window and i will show the results below

    Val("198.40")
    198,4

    Val("198,40")
    198

    CDbl("198.40") => Produced the Type Mismatch Run-Time Error 13

    CDbl("198,40")
    198,4


    So the CDbl("198.40") produced the type mismatch error...........still not sure why really
    and the Val did not produce any error. so thats good right?

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    The VAL result is only any good if you don't mind the value being different to the amount you thought it might be.

    For me , and . return values without error. But I don't have Polish regional settings.

    This help page mentions not to use VAL.
    https://docs.microsoft.com/en-us/off...sion-functions

    Add here is lot more info on regional isses.
    http://www.oaltd.co.uk/excelprogref/...rogrefch22.htm

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,722

    Re: CDbl giving type mismatch error

    Works for me...

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  16. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    note: all of the work i did in the immediate window was done on friends computer in poland..........they did it and sent me screen shots.
    that work was not done on "my computer in the USA" but rather on "a computer in poland"

    Please Login or Register  to view this content.
    did NOT work on the computer in poland it gave a type mismatch error
    on MY computer the code above does work


    andy, i have checked several files after running using the VAL function and it seems to give the right result. Note: for some reason the numbers in the "file" that the macro is
    processing has "." and not "," I am checking each number and if one exists that has a "," in it i convert it to a "." if i do this i think i will be ok but i will read
    the links you sent before i decide for sure......if i dont use VAL what should i use? do i even have an alternative?

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    If you can determine the locale then you could do the , . replacement before CDBL.

  18. #18
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: CDbl giving type mismatch error

    @welchs101;

    The code in message #7 should work in both US and Poland locales ... Did you check it ?

    It should get a valid CDbl value (198.40 or 198,40) depending on the locales.
    Last edited by Haluk; 07-28-2020 at 10:30 AM.

  19. #19
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    ?How would knowing the local help me? Also, how to even identify the local within excel-vba?

    What i am doing right now is this...........i look to see if the "3rd" character in the number is a "comma".

    if it is then i convert this comma to a "." but leave all other commas if they are present. Then i use the VAL command.......

    But i still dont understand why the CDbl gives a mismatch error...........do you get why this is so?

  20. #20
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    Haluk, i have not tried it .....i will try it .....if i cant contact my friend i will try tomorrow and let you know.

    thanks.

  21. #21
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    determine which character is what
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    Andy thanks , i did not know this even existed.

    Haluk, i tried your code on my machine...works fine. and you're right it should work on computer in poland so long as i do a
    little checking on value to make sure it has a decimal separator and not just an integer value.

  23. #23
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    again, thanks for everyone's input but i just wanted to ask this again so i understand something.

    If i used CDbl("198.40") on PolandCOmputer.........i get type mismatch

    so why dont i get a similar error when i do CDbl("198,40") which equal 19840 on my USAcomputer

    and just to be clear........do you all agree that i can use "VAL" as long as i remove the decimal separator if its a comma?
    it seems to work anyway i just have not used it as much as the CDbl
    Last edited by welchs101; 07-28-2020 at 11:31 AM.

  24. #24
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    not sure if this is applicable or even correct but i found this statement online and wanted to share

    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    andy, i was going back and reviewing the posts........in Post@17 it sounds like you're saying to convert "," to "." (if there is one) and then use the CDbl.......is this right?

    Because if it is this is what i tried in the beginning and it did not work.

    recall that you suggested i use the intermediate window to test the CDBL("198.40") on the polandcomputer and it gave a type mismatch error

  26. #26
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,772

    Re: CDbl giving type mismatch error

    It would be the other way. Replace the decimal point in the text with , before CDBL. put this would be on the Polish PC

  27. #27
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit
    Posts
    706

    Re: CDbl giving type mismatch error

    Quote Originally Posted by welchs101 View Post

    ....... so long as i do a little checking on value to make sure it has a decimal separator and not just an integer value.
    If it looks like an integer, you can add a period and then use the same code;

    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    hey, let me ask a dumb question.........if i ask my friend to change their region settings...........would that fix the problem as well?

  29. #29
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,449

    Re: CDbl giving type mismatch error

    fyi: i tried changing decimal separator from "," to "." on polandcomputer and this did not work..........


    "File >> Options >> Advanced > Editing Options [Uncheck the box "Use system operators"] and change the settings." this did not work



    do i need to change the "REGION" setting or somethign?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] COUNTIF function giving Type Mismatch error
    By Utzja1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2016, 07:34 AM
  2. Instr() function giving error: "Type mismatch"
    By arjun.majumdar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2013, 04:17 AM
  3. Replies: 4
    Last Post: 03-27-2013, 12:16 PM
  4. Iserror is giving me a type mismatch
    By BeefNoodleSoup in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2012, 01:05 AM
  5. Complile Error: Type Mismatch ??? After adding error trap
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2012, 03:50 PM
  6. Conditional Formatting - Run Time Error '13' Type Mismatch Error
    By ksp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2011, 07:37 PM
  7. Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1