Hi Thai,
Something like this requirement can be done in very many ways in VBA. I only know a few of the ways.
I find it very difficult to understand and follow the logic of your coding.
I think it is always difficult to understand other people’s code.
So for now I will start from the beginning with a new code.
It is not 100 % clear to me exactly what output you want. It appears that you want to show the new data in the second sheet, along with the original data… This seems strange to me?
For testing purposes, I will give the output a little bit different to how your code does it, because that makes it a bit easier to test for now,
I think you have potentially a very complicated situation. It might be more sensible to insist on certain rules for those adding data, in order to simplify the possible scenarios.
However, I will make some attempt on a code
I give you the code first.
Check if it does what you want.
Then if you need modifications or want me to explain anything , then let me know
I assume a customer will add an entire row or change data . This makes the logic very tricky to get right. Not all possible scenarios will be covered with my code: My code may not work in all possible scenarios
My general code logic:
Both data ranges from workSheet1 and workSheet2 are captured and put into an Array, arrSht1() and arrSht2().
I make a new array, arrSht1b() , which is intended to have possibly extra rows at the place where new rows appear to have been added in workSheet2. So it is given the size of arrSht2()**
Initially the values from arrSht1() are placed in arrSht1b(). ( But as arrSht1b() has the size of arrSht2() , then initially there may be some empty elements at the end, ( end = last rows of data )
I also create an array, arrout() , to be used to hold the required output data.
**Both arrays, arrSht1b() and arrout() , can be given their types ( String ), and final size as that of arrSht2()
I loop through the data in sheet1 ( or rather array, arrSht1b() ) and compare with sheet2 ( or rather arrSht2() ).
( arrSht1b() effectively monitors, ( is adjusted to ) , become equal to the new data as the looping continues) .
A Condition check is made.
__If either cell in a row from the sheet 1 data does not match the new data, AND the next row from sheet1 data does, then I assume a row had data changed, but a row was not inserted.
(arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) = arrSht1b(Cnt + 1, 1) And arrSht2(Cnt + 1, 2) = arrSht1b(Cnt + 1, 2))
For this condition satisfied, the monitoring array , arrSht1b() , is adjusted to the new values and the cell differences checked and counted and the relevant info given to the output array, arrOut().
A second Condition check is made, ( which will only be made else if, ( ElseIf ) , the first was not satisfied)
__ElseIf either cell in a row from the sheet 1 data does not match the new data, AND the next row in sheet2 data does not match the next data from sheet 1, then this is one possibility** that a row has been inserted/ added
((arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) <> arrSht1b(Cnt + 1, 1) Or arrSht2(Cnt + 1, 2) <> arrSht1b(Cnt + 1, 2)))
I then add that data row then to monitoring array, arrSht1b(), after all values above are shifted one place. This modified array is then further used to compare further with array, arrSht2()
At this check , the value at the row in the arrSht2() is added to the Output array, arrOut()
Eventually arrSht1b() will be identical to arrSht2(). But, the important point, in the process of looping it is built, and at each loop is used to check if the current row matches the row in the second sheet data.
_.___________________
This is almost certainly not the most professional or efficient code, and it will not work in all possible scenarios** but may be easier to understand as an initial attempt.
**I have not tested it extensively in all possible situations.: I leave this to you to test thoroughly and tell me if it is working OK for you, ( free excel help forum is for help and to help learn but not to do work for you )
I have not included all your original cell formatting. If you need help with this then ask
For testing purposes, I give the output in a third worksheet, Tabelle3
I will gladly explain the code in more detail if you ask me to.
_._____________
Code is here:
http://www.excelfox.com/forum/showth...0736#post10736
( Run code Sub TestieCalls() )
( code also in uploaded files:
Compare2WorkSheets Testings2.xlsm”
Compare2WorkSheets Testings1.xlsm” )
_.______________________________________
Example testing
For testing purposes, I give the output in a third worksheet, Tabelle3
Test 2 ( “Compare2WorkSheets Testings2.xlsm” )
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue12 ABC456 _____ <> Jet Blue12 _______ <> ABC456 Alaska Jet Blue12 ABC456 Toyota Supra Alaska Emirate ABC12345 Toyota Supra Emirate ABC12345 Spaceship on last row is new 12 on last row is new Spaceship
Testing2.JPG https://imgur.com/ISbeHaJ
Attachment 2081
Test 1 ( “Compare2WorkSheets Testings1.xlsm” )
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC Jet Blue12 <> Jet Blue21 ABC12345 <> ABC Jet Blue12 ABC12345 Alaska Alaska Toyota Supra Toyota Supra Emirate ABC12345 Emirate ABC12345 Spaceship on last row is new 12 on last row is new Spaceship
Testing1.JPG : https://imgur.com/eCjAGOL
Testing1.JPG
_.__________________________-
The above results seem OK....
**But remember: This code does not work in all scenarios. We may need to modify and improve it. You must check all possible scenarios and report back.
Alan
Reply With Quote
Bookmarks