Compare and amend as required

[johnbrown]

Hi,

I'm new to the forum and to excel VBA programming.

I need a quick fix for the following problem. Hoping someone can post the solution as I don't think it'll be to tough for anyone with a little experience in VBA.

As always, these things are easiest explained with example data.

EG:

A1: Name B1: S/N C1: Notes D1: Stock
A2: James B2: 1234 C2: N/A D2: 5
A3: Bob B3: 3452 C3: N/A D3: 7
A4: Mary B4: 1212 C4: N/A D4: 9


Now what I'd like is a button which checks the S/N of each record and compares it to another set of data, say on another sheet, formatted in the same way. If the record in the new sheet exists in both sheets then update the stock level with the stock level from the new sheet. If it doesnt exist then add the record to the sheet.

EG: This info is on sheet 2:

A1: Name B1: S/N C1: Notes D1: Stock
A2: Kyle B2: 4534 C2: N/A D2: 12
A3: James B3: 1234 C3: N/A D3: 2
A4: Keiron B4: 3452 C4: N/A D4: 20
A5: Bob B5: 3452 C4: N/A D5: 10


After the VBA macro is run I'd like the original data to look like this:

A1: Name B1: S/N C1: Notes D1: Stock
A2: James B2: 1234 C2: N/A D2: 2
A3: Bob B3: 3452 C3: N/A D3: 10
A4: Mary B4: 1212 C4: N/A D4: 9
A5: Kyle B5: 4534 C5: N/A D5: 12
A6: Keiron B6: 3452 C6: N/A D6: 20

I hope the examples I've given show what I mean.

Can anyone help me out with this? I'd really appreciate it.

EDIT: Apologies for the formatting of my examples. Sorry

[mrice]

I hope that the following will do what you need.

Please Login or Register  to view this content.

I note that in your example data, the S/N 3452 seems to be shared by two different people so I couldn't reproduce your required output exactly.

[johnbrown]

Thanks for the reply. Sorry about the example data. The S/N was not suppose to be shared between people.

Would it be possible for you to explain the code to me. Just brief comments on each line would be great. This way I at least learn something from this and in future may not need to ask for help.

Thanks again.

[mrice]

Here goes

Please Login or Register  to view this content.