+ Reply to Thread
Results 1 to 4 of 4

Type mismatch error after pasting values

  1. #1
    Registered User
    Join Date
    04-07-2015
    Location
    TX
    MS-Off Ver
    2013
    Posts
    2

    Type mismatch error after pasting values

    Hello all - first post here, but I want to thank you all for advancing my VBA work for quite a while now.

    My VBA code opens approx. 250 files and extracts specific data from select rows/columns in each file based on an assessment of a value in a specific column. I've copied the file opening code and threshold criteria code below. For months, this has worked beautifully.

    Please Login or Register  to view this content.
    I recently wrote a separate code that now precedes the above functions to open the same files, makes a number of calculations and writes new values over the existing columns of my worksheet. This code also seems to be working well. The data formats in the resulting files are all the same as the original versions.

    When I attempt to run my 'threshold flagging' code on the revised files, I receive a type mismatch error on the following line from above...

    Please Login or Register  to view this content.
    When I hover over the 'dresult' when attempting to debug, it shows the first correct value from the first file that was supposed to be opened. I've run both sets of code multiple times this morning...the original code on the unmodified files and the modify code separately. When i attempt to run the original 'threshold flagging' code on the modified files, the type mismatch error appears every time.

    Any help you can offer would be greatly appreciated. I'm happy to paste the entire macro if seeing all of the code is informative, but I thought there may be something simple I overlooked. thanks in advance!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,060

    Re: Type mismatch error after pasting values

    What is displayed if you use this?

    Msgbox d.Value

    You can try casting the value into a double (though it should work as written even if the value is a string or has extra spaces):

    dResult = Round(cDbl(d.Value), 2)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-07-2015
    Location
    TX
    MS-Off Ver
    2013
    Posts
    2

    Re: Type mismatch error after pasting values

    Thank you for the reply, Bernie. Msgbox d.Value returned the 12 decimal number referred to by the code, the rounding function is intended to read all relevant values and round them to 2 decimal places.

    Your casting suggestion returned the same error as the original code (odd), but I wondered if something about the pasted data was being viewed incorrectly versus the original text reports run from our application reporting tool. Your suggestion led me to try out some other quick data conversions and VAL() worked like a charm.

    dResult = Round(Val(d.Value),2)

    Thank you for taking the time to reply, Bernie.

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Type mismatch error after pasting values

    If VAL worked its because the original value likely wasn't a numeric value but a text string. VAL strips out text and returns a numeric value as described here

    Glancing at your code, you should address this before the part you have your issue in. Your if statement uses > and <, which will consider the text version of a value different than the numeric version of the value in comparisons like this. By addressing it later in your doe as you have you run the risk of having values get through your criteria in unexpected ways.

    Your most accurate approach would be to force the entry of numeric values in those cells so that text string may not be entered (IE: " 123 " is text vs "123"). If you cannot address it at the sheet/cell level then you may want to do your comparisons (In your IF/loop) using the VAL function, maybe even combined with CDbl/ IE: If CDbl(VAL(d.value)) >= x.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

+ 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] RunTime Error 13 ( type mismatch ) error is comming TextboxAfter_Update
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2017, 03:55 AM
  2. Type mismatch error when assigning values resulting from a linear interpolation
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2015, 09:01 PM
  3. Runtime Error - Type Mismatch when copying and pasting cells in sheet with macros
    By lhickerson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2014, 01:25 PM
  4. [SOLVED] Run-type error 13 type mismatch
    By misop in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-05-2012, 04:08 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