How to match data, then Copy

[joelle]

Hello Experts,

I hope someone could help me with this quest:

I have 2 worksheets: sheet-A and sheet-B.
Please what is the vba to:

1. match the part# from sheet-B with part# from sheet-A
2. if a match is found, copy the quantity of the matched part# from sheet-B to the qty field of the same part# in sheet-A.
3. Do loop

Many many thanks in advance. I attach a test workbook for more illustration.

joelle

[joelle]

Hello Experts,

I'm under time pressure so any help is very much appreciated.

J.

[herzberg]

I don't think code is needed; a simple Vlookup will suffice. Check out the attached workbook. By the way, I think it's easier to work with a consistent format, e.g. all merged cells or all un-merged cells, not somewhere in between.

[joelle]

Hello Herzberg,

Thanks for looking at my case but I don't think I can get it by without vba.

1. I cannot enter vlookup formula in any cell from either of the worksheets since this mean overriding existing value in the qty col which is not allowed.

2. the qty col from the destination worksheet has merged cells and they have to remain merged

thats why we need vba badly in a case like this.
I'm still sticking my neck out for any help you or someone other can provide.

Pls help.

J.

[herzberg]

Hi joelle, There's something I don't quite understand. Initially, you mentioned the following:


1. match the part# from sheet-B with part# from sheet-A
2. if a match is found, copy the quantity of the matched part# from sheet-B to the qty field of the same part# in sheet-A.
3. Do loop

Wouldn't this mean overwriting the original value(s), when a matched value from Sheet B is copied into the respective quantity cell in Sheet A?

If you want to preserve the original values, you can place the Vlookup formula anywhere else on Sheet A; just remember to change the lookup cell accordingly and it'll return you the matched values.

If you need a sum of the original and the matched values, you can then add in another column to do it.

[joelle]

Wouldn't this mean overwriting the original value(s), when a matched value from Sheet B is copied into the respective quantity cell in Sheet A?

No, this would not mean overwriting the original value in sheetA, because to start with, the qty col in sheetA is empty. Not only that, the qty col in sheetA has merged cells !!! biggest challenge yet.
Then, if macro can match up the part# from sheetB with the part# in sheetA, only then, macro will copy the [qty from B] to the [qty in A] with respect to the matching part#.
Pls take a look at the image attached. And the copy direction is B --> A.
It would just be wonderful to know if vba can do this magic!

[Zack Barresse]

Cross-posted: http://www.mrexcel.com/board2/viewtopic.php?t=300875

Please do NOT cross post.