+ Reply to Thread
Results 1 to 8 of 8

VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

  1. #1
    Registered User
    Join Date
    01-24-2014
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    5

    VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Hi guys

    I have the following code that runs only in debug mode but returns a type mismatch error..

    EndRow = Sheets("Data_Main").Range("D" & Rows.Count).End(xlUp).Row
    c1 = 2

    For k = 2 To EndRow
    If (Sheets("Data_Main").Range("D" & k) = Range("A1")) Then
    Range("I" & c1) = Sheets("Data_Main").Range("A" & k)
    c1 = c1 + 1
    End If
    Next k

    A very similar snippet of code (in the same project) runs perfectly fine outside of break mode.. I'm really puzzled. I know that DataMain is a large dataset (40000) rows or so. Could that possibly lead to this kind of error?

    EndRow = Sheets("Territory_Rules").Range("A" & Rows.Count).End(xlUp).Row
    c1 = 2

    For k = 2 To EndRow
    If (Sheets("Territory_Rules").Range("A" & k) = Range("A1")) Then
    Range("A" & c1) = Sheets("Territory_Rules").Range("B" & k)
    Range("B" & c1) = Sheets("Territory_Rules").Range("C" & k)
    Range("C" & c1) = Sheets("Territory_Rules").Range("D" & k)
    c1 = c1 + 1
    End If
    Next k

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    First of all, please wrap code in CODE tags - it makes it far easier to read.

    Now, you haven't pasted all your code, so we can't see how your variables are declared.

    Are variables EndRow and k declared as Integers, or Longs? Integers have a limit of 32,768, so if you're trying to assign a value of ~40,000 to an integer variable, it will fail... The same code would of course still work for endrow values <=32,768!
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    01-24-2014
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Hi, thanks for your response! k and EndRow are both declared as Long!

  4. #4
    Registered User
    Join Date
    01-24-2014
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Here's the rest of the code! I've highlighted the problem area..

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Okay, so it's struggling to compare cells. Try appending .value to each range reference, so it compares the CONTENTS of cells, regardless of the format:

    e.g.
    Please Login or Register  to view this content.
    Oh, and c1 is a confusing name for a variable - as it's also a cell reference...

    Failing that, can you post a sample workbook containing all your code.

  6. #6
    Registered User
    Join Date
    01-24-2014
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Thanks for your suggestion! I implemented it and got the same error.. Do you have any other ideas? I don't want to post the sheet because it's for a marketing class, and we're working with client information..

    All of the cells are formatted as "general." I can't think of why this wouldn't work..

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    Really hard to try and debug further without seeing the workbook...

    Have a look at which value of k causes the type mismatch failure, and inspect the corresponding cells...

  8. #8
    Registered User
    Join Date
    01-24-2014
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode

    ahh ok, thank you very much!! some of the data values in the column were #N/A.

    thanks again

+ 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] Code skips on Run Mode but runs fine on F8 (Debug Mode)
    By RaquelAR in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-25-2013, 01:05 PM
  2. Replies: 3
    Last Post: 03-29-2012, 01:58 PM
  3. VBA code only works correctly in Debug mode
    By tpthatsme in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2012, 12:51 PM
  4. Debug error '13' - Type Mismatch
    By Charlottewildsmith in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-30-2010, 02:53 PM
  5. [SOLVED] [SOLVED] Excel 2000 Code works except in debug mode
    By Bob Smedley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2006, 09:25 PM

Tags for this Thread

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