I got a template layout and i normally copy and paste all customer data in my template and saved. However, a lot of time customer send me same data but they added in few new data. But some of the old data is not the same row or cell as the old one.
I have code that scans and compares old excel list with new excel list, and then pastes all of the differences between the two into a new sheet. My code is working properly - However, This code is only compare cell by cell. It don't work if there is a new row inserted between old rows.
How can I improve the code to look only for new data added in the new excel file and avoid display all the same data that listed on different row? What should I change or add in my code?
I've tried researching on Google and Stack Overflow, but couldn't find much relevant information.
Here is my full code (let me know if you need me to provide more):
image(1).jpgimage.jpg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHACode:Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet) Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String Dim report As Workbook, difference As Long Dim row As Long, col As Integer Set report = Workbooks.Add With ws1.UsedRange ws1row = .Rows.Count ws1col = .Columns.Count End With With ws2.UsedRange ws2row = .Rows.Count ws2col = .Columns.Count End With maxrow = ws1row maxcol = ws1col If maxrow < ws2row Then maxrow = ws2row If maxcol < ws2col Then maxcol = ws2col difference = 0 For col = 1 To maxcol For row = 1 To maxrow colval1 = "" colval2 = "" colval1 = ws1.Cells(row, col).Formula colval2 = ws2.Cells(row, col).Formula If colval1 <> colval2 Then difference = difference + 1 Cells(row, col).Formula = colval1 & "<> " & colval2 Cells(row, col).Interior.Color = 255 Cells(row, col).Font.ColorIndex = 2 Cells(row, col).Font.Bold = True End If Next row Next col Columns("A:B").ColumnWidth = 25 report.Saved = True If difference = 0 Then report.Close False End If Set report = Nothing MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets" End Sub Private Sub CommandButton1_Click() 'Compare2WorkSheets Worksheets("Sheet1"), Worksheets("Sheet2") Set myWorkbook1 = Workbooks.Open("C:\familycomputerclub-website\Excel2007 \testcompare2.xlsx") Compare2WorkSheets Workbooks("testcompare1.xlsm").Worksheets("Sheet1"), myWorkbook1.Worksheets("Sheet1") End Sub
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?f=26&t=26183
https://eileenslounge.com/viewtopic.php?f=26&t=26030
https://eileenslounge.com/viewtopic.php?p=202322#p202322
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396
https://shorturl.at/FGIKO
https://shorturl.at/gmrY0
https://eileenslounge.com/viewtopic.php?f=44&t=26076
https://eileenslounge.com/viewtopic.php?p=200050#p200050
https://eileenslounge.com/viewtopic.php?f=27&t=25599
https://eileenslounge.com/viewtopic.php?f=30&t=25460&p=197549#p197549
https://eileenslounge.com/viewtopic.php?p=196259#p196259
https://eileenslounge.com/viewtopic.php?p=196446#p196446
https://eileenslounge.com/viewtopic.php?f=27&t=25140&p=195883#p195883
https://eileenslounge.com/viewtopic.php?f=30&t=25213&p=195477#p195477
https://eileenslounge.com/viewtopic.php?p=195462#p195462
https://eileenslounge.com/viewtopic.php?p=193781#p193781
https://eileenslounge.com/viewtopic.php?p=193403#p193403
https://eileenslounge.com/viewtopic.php?p=208624#p208624
https://eileenslounge.com/viewtopic.php?p=177253#p177253
https://eileenslounge.com/viewtopic.php?f=26&t=22603
https://eileenslounge.com/viewtopic.php?p=175751#p175751
https://eileenslounge.com/viewtopic.php?p=175902#p175902
https://eileenslounge.com/viewtopic.php?p=289020#p289020
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Bookmarks