+ Reply to Thread
Results 1 to 16 of 16

Type mismatch error for Vba macro

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Type mismatch error for Vba macro

    hi i have a macro and i did all i can but i cant get through the error of a type mismatch. Please help! Thanks!

    my macro:

    Please Login or Register  to view this content.

  2. #2
    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,091

    Re: Type mismatch error for Vba macro

    I'm guessing this:

    Please Login or Register  to view this content.

    Should be this:

    Please Login or Register  to view this content.

    And lose the second End If


    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


  3. #3
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    Without the IF, how do i compare the data between two worksheets? I want to make it such that when I have the "Done" comment with one of the rows, the other corresponding row of the other worksheet with the same Index number and stuff are deleted

  4. #4
    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,091

    Re: Type mismatch error for Vba macro

    So is a variant variable with nothing in it. With an If, you are comparing an empty variable with whatever is returned by the VLOOKUP. Without an If, you are setting the variable to whatever is returned by the VLOOKUP. Did you try it? And, if you tried it, what happened?

  5. #5
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    yes i did.it wiped out the whole list of data instead. I only one those that has the word "done" in the row be wiped off.

    Do u mean SO should take the value whatever vlookup gives back? If so, how does the macro know that those row with the value of SO in their row/column should be wiped off?

  6. #6
    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,091

    Re: Type mismatch error for Vba macro

    It looks to me as though the SO variable should take whatever is returned by the VLOOKUP and, on the next line it compares it to Res which contains "Done".

    Difficult to tell without data. You don't actually say where the error occurs. Has that gone away? I can't see how it would just clear everything.

  7. #7
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    OH, so u do mean i still need the line IF Res= SO then .....?

  8. #8
    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,091

    Re: Type mismatch error for Vba macro

    It's actually SO = Res, but yes, you still need it. I said edit the line and remove the End If. I didn't say remove that check.

    But actually, it looks as thought that may be S0 = Res. Put Option Explicit at the top of the module and try to compile the project.

  9. #9
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    so it should be like this?



    Please Login or Register  to view this content.

    Thanks for your help! I ran with this macro, it wiped out my target worksheet totally.

  10. #10
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    Ignore the previous post please.so it should be like this?



    Please Login or Register  to view this content.
    Now the type mismatch error is at the IF Res= SO Then line..
    Last edited by alonso1111; 07-25-2014 at 03:59 AM.

  11. #11
    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,091

    Re: Type mismatch error for Vba macro

    Your comment says it's looping down column 4. it's not, it's column 1.

    Please post a sample workbook.

  12. #12
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    samplewb.xlsxsamplewb.xlsx

    Ok i uploaded a sample wb. Hope it helps.

    Basically, the Items number are unique and I hope to use them as a cross reference between the two sheets and run through th entire sheet. And with this, if the column has the word "done" on it , then the item number corresponding to it will be wiped out on Sheet1, leaving the rest there. and that's about it. Thanks!

  13. #13
    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,091

    Re: Type mismatch error for Vba macro

    Maybe this:

    Please Login or Register  to view this content.

    Regards, TMS

  14. #14
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    Sorry for the late reply, would u mind explaining the code? because its totally different from mine. Thanks!

  15. #15
    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,091

    Re: Type mismatch error for Vba macro

    Does it work? With your data, I mean.

    It determines the last data row. Then, based on that, it puts a VLOOKUP formula in column Z (arbitrarily chosen). It converts the results to values but it doesn't really need to. Then it loops through the rows from the last one to row 2. If the value in column Z is "Done", it blanks out the first 10 columns. Alternatively, you could delete the entire row. Then it deletes column Z. if you choose to delete the whole row, that would be redundant.

    Regards, TMS

  16. #16
    Registered User
    Join Date
    06-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Type mismatch error for Vba macro

    Hi, Yes its working. Thanks. However, it still says type mismatch when i use it in another similar worksheet.

+ 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. Type Mismatch error - extracting number macro
    By missy22 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2014, 05:53 AM
  2. [SOLVED] Concatenate macro Type Mismatch error
    By rdowney79 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-12-2014, 02:05 PM
  3. Getting Type Mismatch, Runtime Error 13 With This Macro
    By HowdeeDoodee in forum Excel General
    Replies: 16
    Last Post: 11-13-2012, 07:59 AM
  4. Re: Help with a macro- 'Run-Time error '13' Type Mismatch
    By pman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2012, 03:31 PM
  5. type mismatch error in Benford's law macro
    By Sody in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2006, 06:10 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