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.
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.
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
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
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?
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?
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.
OH, so u do mean i still need the line IF Res= SO then .....?
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.
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.
Ignore the previous post please.so it should be like this?
Now the type mismatch error is at the IF Res= SO Then line..Please Login or Register to view this content.
Last edited by alonso1111; 07-25-2014 at 03:59 AM.
Your comment says it's looping down column 4. it's not, it's column 1.
Please post a sample workbook.
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!
Maybe this:
Please Login or Register to view this content.
Regards, TMS
Sorry for the late reply, would u mind explaining the code? because its totally different from mine. Thanks!
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
Hi, Yes its working. Thanks. However, it still says type mismatch when i use it in another similar worksheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks