Compare and React

Hello,

I am trying to compare a string of data. If the string in sheet 1 finds a match in the string of sheet 2, then I want it to compare the qty field of both strings. If the two strings match and the qty fields match, move on. If the qty fields do not match then copy the entire row and paste to a 3rd sheet.

I have the code that lets them compare, but I get stumped after that with copying the entire line and pasting to new sheet and moving on. I know this code is poor so any help on cleaning it up is appreciated. Thanks

Kelly

Please Login or Register  to view this content.

This will show my whole code and what I am trying to do. Thanks for the help.

Kelly

Please Login or Register  to view this content.

Declare My Variables

Please Login or Register  to view this content.

Define Sheet1 Size

Please Login or Register  to view this content.

Identify on first sheet the Order, Line and Delivery Number. Then the Qty, in cell AF, that is associated with that Delivery. I know I have something wrong here, but I am trying to get the system to Idenfity the first line, create the string then move to the 2nd sheet for comparison.

Please Login or Register  to view this content.

Define sheet2 size

Please Login or Register  to view this content.

create a second string from the second sheet with the Order Number, Line Item and Delivery Number and find the Qty value in cell AF

Please Login or Register  to view this content.

If the first string is found within all of the thousands of Orders on the second sheet and If both Qty's match, move on to the next order. If the Qty is different then take the whole line of the second string and paste it to a 3rd sheet titled "Difference".

I hope that this helps. Thanks

Kelly

hi, can you post a sample workbook for a closer look?

Attached is an example. I appreciate any help. Thanks

Kelly

Bumping this up. I appreciate any help

hi, there is no attachment

Sorry, now it is attached.

Hi Watersev,

Thanks for this. I read above in my first post that this is exactly what I asked for. So I can mark this as closed if needed. If possible I would like to get an explanation of how this code is working so I can make some tweaks. I have tried and watched the variables and I admit, I can't make heads or tails out of how it looks at the "Qty field".

What if I want it to look at the Date Fields as well (Columns M-P)? How can I expand on this? Also, Would it be possible to change the font color to red for the values that have changed. With thousands of lines this will make it much easier for users to find the changed data if we can analyze more than just the Qty field.

Thank you so much.

Kelly

'The whole code is based on the Find Next method shown in VB help file.

Please Login or Register  to view this content.

'Creating objects we will work with

Please Login or Register  to view this content.

'i variable defines row number on Difference sheet where we will paste found row, it increases each time by one

Please Login or Register  to view this content.

'That's the main comparing string. It compares J2 offset 1 column (i.e. 1 column to the right cell), J2 offset 2 columns and J2 offset 22 columns to the corresponding values - Qty field you were looking for

Please Login or Register  to view this content.

'The line responsible for pasting found rows, it's pretty straightforward

Please Login or Register  to view this content.

Watersev,

Thank you again for all of your help and explanation. Attached I have put in my udpated code and I think I am on the right track to make a couple of modifications.

I understand comparison to the Qty field, but now I am expanding it to compare the Demand Date and Ship Date as well.

On top of that, I want it to change the color of the field that is changed. So the attached file I think gets me a lot of the way there. 2 issues have come up.

1. It always colors the Qty field red, even though I tell it to color it back to black at the end of the sub-routine before looking at the next field. So I suspect that both workbooks are in memory and no matter what it is pasting that. Not sure how to get it to clear the memory at the start of each routine.

2. It overwrites itself at the start of each routine, and I need it to append on worksheet 3. This one I may be able to fix.

Thanks for all of the help.

Kelly

Watersev,

Thank you very much. I appreciate the help. This worked perfectly.

Kelly