Find a Match and Copy

[aviatecar]

Hi Gurus:

I was trying to do the following but it is a bit too crazy to just do it by hand.
So I am wondering if anyone can write me a code to help me finish this piece of find,copy and paste job.

Here is what I wanna do:
{
Start from cell D1 in sheet 2.
Search the value of cell D1 of sheet 2 from entire column B of sheet 1

If a match is found at Bi from sheet1,
Do the follwoing:
Copy the content of cell E1,F1,G1,H1 from sheet2 into cell Ci of sheet1
Copy the content of cell J1,K1,L1,N1,O1 from sheet2 into cell Oi,Li,Mi,Ni,Di of sheet1 respectively
Copy the content of cell Q1,R1 from sheet 2 into cell Ui of sheet1

If no match is found, do nothing.

Repeat the same steps for D2 in sheet 2 until the end of column D of sheet 2.

}

Sample file.xlsx

Plese refer the attachement for better illustration.

Many many thanks,

Chris

[xladept]

Hi aviatecar,

You'll need to put this code in a module and save your book with the .xlsm extension:

Please Login or Register  to view this content.

[xladept]

Correction:

Please Login or Register  to view this content.

[aviatecar]

Hi xladept:

I appreciate your help.
The code seems work but it just doesnt put things into right cells.
Would you check the match our your code with my description please?!

Thank you ,

Chris

Correction:

Please Login or Register  to view this content.

[aviatecar]

So I did minor modification and things go to the right cells.
However, I want the content of source cells(sheet2) to be added to the goal cell content (sheet1). That means I need to keep all of the origional information in sheet 1.

How can I do it?

Many many thanks

Chris

[xladept]

Hi Chris,

Change the routine to copy everything.

Please Login or Register  to view this content.

[aviatecar]

Hi Chris,

Change the routine to copy everything.

Sorry? I dont understand...

[JBeaucaire]

This should do it, but note, there is currently NO MATCHING DATA in sheet1!B:B and sheet2!D:D, so I put some matches in and ran it.

Please Login or Register  to view this content.

[aviatecar]

OHHH! Got it.
Thanks xladept!

Hi Chris,

Change the routine to copy everything.

Please Login or Register  to view this content.

[JBeaucaire]

Take a look at the post #8 when you get the chance, too.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

[aviatecar]

Hi JB:

I am actually trying to give it a whirl but it seems that it would run the code for ever...

Anything I can do?

Chris

Take a look at the post #8 when you get the chance, too.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

[JBeaucaire]

...but it seems that it would run the code for ever...

Explain what you mean by "seems"? You "think" it might run forever, or you tried it and it did?

The code makes a RNG of the values on Sht2 column D, only the cells with a value in them, so this nicely excludes all empty cells. Then it does a single FIND for each value on Sht1 column B, it finds the value or doesn't in one try. If it does find it, it copies data over, if it does not, it moves on, no need to check every cell on sht1 individually.

[aviatecar]

Perfect logic.

However, I ran the code and it didnt finish until I stopped it after 10 minutes. There was only 6k some thing rows a few columns as you might see from my sample.

Please take a second look.

Thanks

Chris

Explain what you mean by "seems"? You "think" it might run forever, or you tried it and it did?

The code makes a RNG of the values on Sht2 column D, only the cells with a value in them, so this nicely excludes all empty cells. Then it does a single FIND for each value on Sht1 column B, it finds the value or doesn't in one try. If it does find it, it copies data over, if it does not, it moves on, no need to check every cell on sht1 individually.

[JBeaucaire]

You had a different file with more matching data I could take that look on? I'm imagining your column D on sheet2 has more cells with data in it than appears. Also, stopping a macro doesn't make it complete, it stops it, so it should have been mid-macro.

Use F8 to step through the macro and after it passes the Set vRNG command, press CTRL-G and type this in the Immediate Window:

? vRNG.Address

...and press ENTER. That should tell you the range of cells it's going to evaluate. Is it right or wrong? That could point you to the problem.

[aviatecar]

an updated file is attached.

Thanks JB for being so helpful!

You had a different file with more matching data I could take that look on? I'm imagining your column D on sheet2 has more cells with data in it than appears. Also, stopping a macro doesn't make it complete, it stops it, so it should have been mid-macro.

Use F8 to step through the macro and after it passes the Set vRNG command, press CTRL-G and type this in the Immediate Window:

? vRNG.Address

...and press ENTER. That should tell you the range of cells it's going to evaluate. Is it right or wrong? That could point you to the problem.

2CT-Parts.xlsm

[aviatecar]

I believe the range of cells is correctly setted.
Not sure whats going on...

an updated file is attached.

Thanks JB for being so helpful!



Attachment 165966

[JBeaucaire]

Wowser... yeah, lots of issues with this data.

You have cells with no value in column D that are not actually blank. So they are being included in the vRNG of cells to evaluate and they shouldn't. Something about the way your data has been entered has NULL values in those cells. So that's easy to workaround:

Please Login or Register  to view this content.

But something else is up here, too. Each FIND command should execute in a second, but it's taking almost a minute for EACH one to execute. Your data seems to cause this method to take much more time than I've ever seen before. It seems likely it is unsuitable. I'm sorry I can't fathom why.

[aviatecar]

So you r suggesting the it would take almost 10 hours to finish 600 sets of data??

Wowser... yeah, lots of issues with this data.

You have cells with no value in column D that are not actually blank. So they are being included in the vRNG of cells to evaluate and they shouldn't. Something about the way your data has been entered has NULL values in those cells. So that's easy to workaround:

Please Login or Register  to view this content.

But something else is up here, too. Each FIND command should execute in a second, but it's taking almost a minute for EACH one to execute. Your data seems to cause this method to take much more time than I've ever seen before. It seems likely it is unsuitable. I'm sorry I can't fathom why.

[aviatecar]

I tried the new code but it deleted origional content when adding new content. Any way to just new add things around while keep old content?

OHHH! Got it.
Thanks xladept!

[xladept]

Hi Chris,

You're welcome.

[xladept]

Try this:

Please Login or Register  to view this content.

[aviatecar]

Hi xladept!

I really appreciate you help but I ve done that piece of job by hand(OMG).

However I ll keep this piece of code for future use

Thank you again for your help!

Chris

Try this:

Please Login or Register  to view this content.

[xladept]

Hi Chris,

By hand - WOW! Well at least as long as the records are sorted this version runs quicker by a magnitude.

[aviatecar]

Thats great! I will use the new code when I am doing sth similier this summer. Thanks agian xladept!

[xladept]

You're welcome!