match data, then copy

[Nee]

Hello Experts,

I hope this is quite simple for you vba experts but I'm a very beginner so please help!

I have 2 worksheets: sheet-A and sheet-B.
Please how do I:

1. match the part# from sheetA (col B) with part# from sheetB (col A), then
2. if a matching part# is found, copy the qty of the matched part# from sheetA to the qty field of the matching part# in sheet-A.
3. Do loop

I attach an img for more illustration of the above.

Many many thanks in advance.
N.

[rylo]

Hi

Do you have a particular requirement that it must be a macro when you could achieve the result using Functions?


rylo

[Nee]

Hello Rylo,

So glad to see your msg!
Yes, I do need vba because the sheetB with the destination qty col is actually a big sheet with 1200 lines.
sheetA, however, has about 50 lines for the products that customer orders.
Then, what I try to do is, if the part# match, the qty should be copied to the qty field of sheetB on the row that has that particular part#. A brief illustration below:

sheetA
part# qty
xxx102 2

sheetB (before macro)
part# qty
xxx102 (blank)

sheetB (after macro, part# xxx102 matches)
part# qty
xxx102 2

I really really do hope you/others can help me with this vba code.

N.

[mikerickson]

You can achieve that result without VB code.
Spreadsheet functions are much faster than VB code and are prefered unless there is an overiding reason not to.

In your case, putting =SUMIF(SheetA!B:B,A2,SheetA!A:A) in SheetB C2 and using fill down to fill the rest of column C will get the result you desire.

[Nee]

Mike,

Pls look at the img I attached earlier. I do really need vba:

1. You're right - I have an overiding reason not to be able to use the formula sumif

2. the destination "qty" col in sheetA consists of merged cell, while the qty col from sheetB has single cell for each qty.

thats why I need vba code badly for this case. I can see now it is a challenge and very interested in knowing if vba can do something for this.

Many thanks to you and others for any hint in helping me with this roadblock.

N.

[rylo]

Hi

Not really obvious from the image that there are merged cells involved.

Can you zip up an example workbook showing both sheets and explaining what should go where so we can review.


rylo

[Nee]

Hello Rylo,

I saw your many other posts while helping others, so I'm so glad you come back to my post.

And I attach here a simple WS with hope my headache could get solved.
Many thanks.

[rylo]

Hi

Have a look at the attached.

I've split your sheet test sheet into 3 new sheets.
sheet1 is your sheetA
sheet2 is your sheetB
sheet3 is a another version of sheet1.

Sheet2 is the data sheet for both methods.

Sheet1 uses formulas to bring back the data.

Sheet3 uses the macro aaa to bring back the data.


HTH

rylo

[Nee]

Hello Rylo,

Thats very fast and many thanks again for your donated code.
It works well with each merged cell in sheet3. In reality though, the qty fields there consist of merged cells of different sizes. So I added some single cells for the qty col; it still works but somehow one part# (xxx117), although matched, is ignored by macro? (pls see zip file)

Also, please would you explain why we set i = 4 from one bit of your code below.
For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row Step 2

Your time is precious so I truly appreciate your help this far.

N.

[Nee]

Hello Rylo,

I looked at your code again and changed "step 2" to "Step 1"

For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row Step 2
changed to
For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row Step 1

and everything works like in dream !!!
despite the fact that I have merged cells of 2-cells-in-one or merged cells of 5-cells-in-one.

I thought this would not be achievable. You are AMAZING Rylo !
You are an expert, but to me, this is magic (single cell data copied to different-sized merged cells! just magical)

Many thanks again !!!!!!!!!!!

N.

[rylo]

N

No worries. Glad you got it going.


rylo

[Nee]

Dear Rylo,

You have been spending your precious time looking at our files, spreadsheets, and you help us big time with the codes you write.
Very unselfish and very kind of you!

I'm sure a lot of us who post (of course myself) truly appreciate your kindness.

Have a great weekend,

Nee