macro to search column value and if it is available in the target sheet then delete row

[Rem0ram]

Hi Excelions

I have 2 excel file as below.

1st file - MIS - with 2 sheets 1. AU 2. IU
2nd file - UserList - with 2 sheets 1. AOU 2. IOU

My goal is - sheet IU got values then i need this IU values to be checked against sheet AOU, if the value available in AOU then delete the entire row and then append the list of IU values in IOU sheet.

Best
Ram

[Rem0ram]

Please advise if my requirement is not clear....

[Rem0ram]

Attached on sample excel file where i combined both files sheet.

To keep it simple

If there is value in sheet IU then i want to check each user id of sheet IU in sheet AOU, if it is available then i want to delete those user id in sheet AOU.

In the example sheet i got user_1 and user_4 in sheet IU and these 2 users available in sheet AOU.

I want to sequentially check the values and delete the entire row in sheet AOU.

Best
Rem0

[Rem0ram]

HI

can someone help to solve this?

[Rem0ram]

Bump....!!!!

[Rem0ram]

Forum Gurus/Moderators

Its really important as im closing the deadline to fix this..

If someone could guide me or point me in right direction or any reference that might help solve this that would be really helpfull..

Thx in advance...

Best
Rem0

[alansidman]

RemO;
Firstly, I suggest you read the forum rules about bumping threads. Look here -->http://www.excelforum.com/forum-rule...rum-rules.html

and I point you to this

If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

It is impolite and rude to keep bumping in the same day. This is a worldwide forum of people who volunteer their time when they wish. It does not work on your schedule or time zone.

Now after all that; I have a proposed solution for you based upon my understanding of what you have explained.

Please Login or Register  to view this content.

[Rem0ram]

Bump...!!!

[Rem0ram]

Hi Alan

Apologies!....agree will follow the rules now on...

Best
Rem0

[Rem0ram]

Hi Alan

I tried using the code....but it actually deletes row in sheet IU, whereas i want to delete the rows in sheet AOU and then move all the values of IU to sheet IOU.

Hope it is clear....

Let me kw if i need to be more specific.

Thanks a lot for the response though, i will keep tweaking the code incase if i get it right will post the same.

Best
Rem0

[Rem0ram]

Found below might cause the deletion in sheet

Please Login or Register  to view this content.

changed to

Please Login or Register  to view this content.

But still got error where in sheet AOU user_2 got deleted where as it is not there in sheet IU.

Best
Rem0

[alansidman]

Try this:

S

Please Login or Register  to view this content.

[Rem0ram]

still no success....

its still deleting user_2 in AOU sheet.

Also, in sheet IOU i want to have only the values of sheet IU after deleting those values in sheet AOU.

Best
Rem0

[alansidman]

Ok. Think I have it now. Sorry. A little bit of coding issues that have to be resolved.

Please Login or Register  to view this content.

[MrDeMolay]

Not to Hijack the Thread,

Would this work if I have 8 Sheets within the Same Workbook, A - H

A is the shortest, and H the longest, but If they're in A they can't be in B - H

B Searches A - If found, Delete from B,
C searched A, and B if found Delete from C

all the way through H seaching A - G and deleting from H.

Is that possible with this code?
I didn't want to start a new thread if this was the same problem I'm having.

Thanks!
Macro.txt
I've attached the code i'm using in a text document,

[alansidman]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

[Rem0ram]

Hi Alan

Still same error deleting User_2 from AOU sheet.

Best
Rem0

[alansidman]

Rem0
My apologies. I had the match line picking up the wrong sheet. I tested this one and it works.

Please Login or Register  to view this content.

[Rem0ram]

Hi Alan

Many Thanks!

Now my challenge is how to make this work if AU , IU in workbook and AOU, IOU in another workbook saved in a shared location.

Also, both the workbook will be shared for multiple user access.

Best
Rem0

[Rem0ram]

Alan

Trying to understand the logic of the code.

Could you please brief the logic behind the code so that i can amend to my use, when you have time?

Best
Rem0

[alansidman]

See the comments inserted into the code itself. I hope this helps. Try to amend the code yourself and post back in a new thread with any issues you may encounter.

This link may help you to understand how to do that.

http://en.kioskea.net/faq/24666-exce...other-workbook

Please Login or Register  to view this content.

[Rem0ram]

Many Thanks Alan!

I just amended the sheet name but i'm getting typemismatch error.

occurs at

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[alansidman]

When you get the error and click on debug, what line of code is highlighted?

Did you define w2 as a worksheet and set it to a particular worksheet. w2 is part of the match formula.

[Rem0ram]

Hi Alan

Here the error occurs in the line highlighted below

Please Login or Register  to view this content.

and sheet w2 is referred as w5 here and it was declared as worksheet.

Best
Rem0

[alansidman]

and sheet w2 is referred as w5 here and it was declared as worksheet.

I don't understand what you mean here. Should the match formula reference w2 or w5? You have confused me.

[Rem0ram]

Hi Alan

the match formula refer w5 here.

Apologies for wrongly pasting the error line it should be as below.

Please Login or Register  to view this content.

[alansidman]

When you change it to w5 does it run as desired?

[Rem0ram]

Nope...Getting Run Time Error 13!

Type MisMactch Error

[alansidman]

Suggest you post the file and code for analysis. Difficult to determine what the error is without seeing the whole picture.

[Rem0ram]

files updated.

[Rem0ram]

Conso is the file where the user id get added and while running the macro it should check in oml sheet and append accordingly.

[alansidman]

Ok. Will look at this over the weekend as I have time. It may take awhile as you have hard coded some paths between the files and I will need to amend those to make them work on my machines. It is also a bit confusing as you have defined the sheets with two different alias'. May be easier if they are consistent throughout.

[xladept]

Hi Rem,

I did this - which shouldn't matter to your code:

Please Login or Register  to view this content.

AND, it ran with no problem???

[Rem0ram]

Hi Xladept

Im getting the same error still!

Rem0

[Rem0ram]

Also

I amended the below line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but it did not work and got script out of range error.

I cannot declare the name of workbook, becoz "Conso" keep on changing.

[Rem0ram]

Hi Alan

Any Luck?

[xladept]

Attach another pair of sample workbooks. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data. And, please be sure that it fails when you run it!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Rem0ram]

Hi Xladept

The sample file updated in page 2 got enough details.

Let me brief again about my requriements.

The Conso workbook is the one used by the team to update MIS.

The OML workbook will be stored in the designated path.

My Need is when ever there is a User ID added to the ROU sheet in Conso workbook using a button i should be able to move the ROU details to mDUM Sheet in OML workbook.

Then i need a code to remove those user ids if available in Sheet mAOU of OML workbook.

After removing them from mAOU sheet then i want to add the users in mDUM sheet in to mROU sheet and also clear mDUM sheet.

After successfully removing the users from mAOU sheet and adding removed users to mROU sheet i want to clear the ROU sheet as well.

Let me know if this is not clear.

Best
Rem0

[Rem0ram]

Hi Xladep / Alan

Any Luck?

Is the explanation given sufficient, i dont see any additional sample file required.

let me know if anything is unclear.

Best
Rem0

[xladept]

Hi Rem,

The problem, for me, is that there's nothing to fix as it ran on my system???