+ Reply to Thread
Results 1 to 14 of 14

If statement .value type mismatch runtime error

  1. #1
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    If statement .value type mismatch runtime error

    Hi, I am having a little bit of trouble fixing a type mismatch error on some code.

    I've tried rewriting it a few ways, but keep getting the same error.

    code is below:

    Please Login or Register  to view this content.
    This is probably a very simple solution, but I am not versed in VBA enough to come to it on my own.

    Thanks in advance.
    Last edited by TheN; 04-10-2017 at 04:06 PM.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: If statement .value type mismatch runtime error

    Kind of hard to say without seeing all the code for that particular If, but, have you tried .Text instead of .Value? Sometimes that works.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: If statement .value type mismatch runtime error

    How is "c" declared? Is it Dimmed as Range? Or is it something else?

    Please post entire code and if possible, sample workbook that represents your workbook set up (with sanitized data).

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: If statement .value type mismatch runtime error

    How is c defined?

    Is it a single cell or mutliple cells?
    If posting code please use code tags, see here.

  5. #5
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: If statement .value type mismatch runtime error

    c is dim'd as range, sorry

    The workbook is too sensitive and far too massive to desensitize. Hopefully I can answer any questions that are needed.

    I also tried .text for the part following the And

    I've also tried using " " around the 1 to see if that helped.

    It worked when I had it comparing to the cell before it instead of a value, but I rewrote the logic of my helper columns so I needed to change the conditions, and now I am getting type mismatch.

    Hopefully that is enough background info?
    Last edited by TheN; 04-10-2017 at 03:59 PM.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: If statement .value type mismatch runtime error

    Any error values in the data?

  7. #7
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: If statement .value type mismatch runtime error

    what do you mean by that? the column it will be looking at (all of the c) will either be numbers or blanks (after my data), there are no #div/0 or anything along those lines.

    Basically I am inserting page breaks whenever the value is 1, if that is pertinent to the solution.

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: If statement .value type mismatch runtime error

    Can you at least post code in it's entirety? Without looking at context of how "c" is used hard to say what's causing the error.

    My guess is that "c" is somehow not pointing to what you think it is... or that you have merged cells in range you are referencing.

  9. #9
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: If statement .value type mismatch runtime error

    I will post a watered down version so you don't have to scroll through loads of code:

    Before I updated the code, when it was comparing if values were the same as ones before them:

    Please Login or Register  to view this content.

    After I updated my helper column to allow for a more complex page break formula:

    Please Login or Register  to view this content.
    I also avoid merged cells like the plague, so that is not the cause.
    Last edited by TheN; 04-10-2017 at 04:28 PM.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: If statement .value type mismatch runtime error

    What I mean is are there any error values in the data, error values are a common cause of a type mismatch error.

    Other common causes are trying to compare multiple cells to a single value, that could be happening here because c isn't single cell or it's part of a merged cell.

    Hard to tell without seeing more of the code, specifically where c is coming from.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: If statement .value type mismatch runtime error

    Do you have more than one sheet selected when you run this code?

    I'm asking that because of the use of ActiveWindow.SelectedSheets here,
    Please Login or Register  to view this content.
    which is normally used when working with multiple sheets.

  12. #12
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: If statement .value type mismatch runtime error

    No, I force it to select the main sheet at start of code, and do not use select any time after that.

    Guess this isn't as simple of a solution as I initially thought.

    Thanks for all the input guys. I thought it was a simple syntax error where I was using .value improperly or something along those lines.
    Last edited by TheN; 04-10-2017 at 04:34 PM.

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: If statement .value type mismatch runtime error

    Code worked fine on my end. Try something like below and see what comes up.

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: If statement .value type mismatch runtime error

    Strange.

    I'll have to look more in depth at it, if it is working for you with a small sample size.

    Thanks for all the help!

    .cells didn't work either unfortunately.

+ 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
    By trobb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-17-2017, 02:06 AM
  2. Type mismatch runtime error 13
    By kalyanr12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2014, 04:09 PM
  3. runtime error 13 type mismatch
    By hughesy8290 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2013, 11:18 AM
  4. Runtime Error 13 Type Mismatch
    By vtsoldier2010 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-30-2012, 12:59 PM
  5. runtime error '13' type mismatch
    By longhorn23 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2010, 04:53 PM
  6. Runtime Error Type 13 Mismatch
    By Linking to specific cells in pivot table in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2006, 12:35 PM
  7. [SOLVED] Runtime Error '13': Type mismatch
    By Linking to specific cells in pivot table in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2005, 03:06 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