+ Reply to Thread
Results 1 to 7 of 7

Formula works but Run Time Error 13 Type Mismatch in VBA

  1. 12-29-2013, 07:26 PM #1
    uberathlete
    uberathlete is offline
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    Formula works but Run Time Error 13 Type Mismatch in VBA

    Hi everyone. This is driving me bananas. Basically, my macro is supposed to enter a formula and copy it down.

    Please Login or Register  to view this content.
    The formula will lookup cell A2 (a part number) from the Parts Prices sheet and return the price, but if there is an error, then lookup the first 8 digits of cell A2 from the Updated Parts Prices sheet.

    The formula works fine on its own but when I enter it as VBA I get a runtime error 13 Type Mismatch. I would really appreciate any help on this. I have attached a workbook.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-29-2013, 07:36 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA

    try
    Please Login or Register  to view this content.
    you need to double up the quotes in the formula
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 12-29-2013, 07:45 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,419

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA

    Maybe:

    Please Login or Register  to view this content.

    As Martin says, double up on the quotes ... but you need a second IFERROR to cope with the failure of the second VLOOKUP.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 12-29-2013, 08:30 PM #4
    uberathlete
    uberathlete is offline
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA

    Thank you for your replies TMShucks and Martin. I tried the double quotes but the formula doesn't seem to work. It returns an error, for some part numbers. So I guess it's not executing the second vlookup in the formula.
    Reply With QuoteRegister To Reply
  5. 12-29-2013, 08:33 PM #5
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA

    the code wont do that, once the formula is in the cell that's it, check the data being looked up
    Reply With QuoteRegister To Reply
  6. 12-29-2013, 08:37 PM #6
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA 

    Please Login or Register  to view this content.
    there was a space either side of the * which i take it you didn't mean to be there
    Reply With QuoteRegister To Reply
  7. 12-29-2013, 10:05 PM #7
    uberathlete
    uberathlete is offline
    Forum Contributor
    Join Date
    10-27-2005
    Posts
    177

    Re: Formula works but Run Time Error 13 Type Mismatch in VBA

    Ah yes you're right. Works perfectly now. Thank you so much!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run-time error '13': Type mismatch
    By Sky188 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 03:07 PM
  2. run-time error '13' type mismatch
    By dp1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-06-2012, 02:43 AM
  3. Run-Time Error 13 'Type Mismatch'
    By Chintanpanchal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2012, 09:30 PM
  4. Run Time Error '13':Type Mismatch
    By rms099k in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2008, 03:37 PM
  5. Run-time error '13':Type mismatch
    By Sibilia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2005, 03:54 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1