Comparing 2 Excel sheets
I have 2 Excel sheets. I need to take 1 value in Sheet 1, look for it in Sheet 2. If I find it, then I need to make sure that some other values are matching. If yes, I copy the sheet 1 row in a "match" tab. If not, I copy the row in "mismatch" tab and I need to insert a message that says which value didn't match. I cannot make it work right now. I think I'm not exiting the loop in the right place. Here is my code. If anybody could help, I would appreciate.
Sub compareAndCopy()
Dim LastRowISINGB As Integer Dim LastRowISINNR As Integer Dim lastRowM As Integer Dim lastRowN As Integer Dim foundTrue As Boolean Dim ErrorMsg As String
' stop screen from updating to speed things up Application.ScreenUpdating = False
'Find the last row for column F and Column B from Sheet 1 and Sheet 2
LastRowISINGB = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "f").End(xlUp).row LastRowISINNR = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "b").End(xlUp).row
'fIND THE LAST ROW OF MATCH AND MISMATCH TAB lastRowM = Sheets("mismatch").Cells(Sheets("mismatch").Rows.Count, "f").End(xlUp).row + 1 lastRowN = Sheets("match").Cells(Sheets("match").Rows.Count, "f").End(xlUp).row + 1
'ISIN MATCH FIRST
For I = 2 To LastRowISINGB
For J = LastRowISINNR To 2 Step -1
If Sheets("Sheet1").Cells(I, 6).Value = Sheets("Sheet2").Cells(J, 2).Value And _
Worksheets("Sheet1").Range("B" & I).Value = "Y" And _
Worksheets("Sheet2").Range("Z" & J).Value = "" And _
(Worksheets("Sheet1").Range("c" & I).Value = Worksheets("Sheet2").Range("AF" & J).Value Or _
Worksheets("Sheet1").Range("K" & I).Value = Worksheets("Sheet2").Range("K" & J).Value Or _
Worksheets("Sheet1").Range("N" & I).Value = Worksheets("Sheet2").Range("L" & J).Value) Then
Sheets("Sheet1").Rows(I).Copy Destination:=Sheets("match").Rows(lastRowN)
lastRowN = lastRowN + 1
Exit For
ElseIf Sheets("Sheet1").Cells(I, 6).Value = Sheets("Sheet2").Cells(J, 2).Value And _
Worksheets("Sheet1").Range("B" & I).Value = "Y" And _
Worksheets("Sheet2").Range("Z" & J).Value = "" And _
Worksheets("Sheet1").Range("c" & I).Value <> Worksheets("Sheet2").Range("AF" & J).Value And _
Worksheets("Sheet1").Range("K" & I).Value <> Worksheets("Sheet2").Range("K" & J).Value And _
Worksheets("Sheet1").Range("N" & I).Value <> Worksheets("Sheet2").Range("L" & J).Value Then
ErrorMsg = "dates don't match"
ElseIf Sheets("Sheet1").Cells(I, 6).Value = Sheets("Sheet2").Cells(J, 2).Value And _
Worksheets("Sheet1").Range("B" & I).Value <> "Y" Then
ErrorMsg = "B column don't match"
ElseIf Sheets("Sheet1").Cells(I, 6).Value = Sheets("Sheet2").Cells(J, 2).Value And _
Worksheets("Sheet1").Range("B" & I).Value = "Y" And _
Worksheets("Sheet2").Range("Z" & J).Value <> "" Then
ErrorMsg = "Z column don't match"
Else: ErrorMsg = "ISIN don't match"
End If
Next J
Sheets("Sheet1").Rows(I).Copy Destination:=Sheets("mismatch").Rows(lastRowM)
Worksheets("mismatch").Range("S" & lastRowM).Value = ErrorMsg
lastRowM = lastRowM + 1
Next I
' stop screen from updating to speed things up
Application.ScreenUpdating = True
End Sub
Hello !
Can you speak French, please ?
Bye !
Oui bien sûr! Désolée
Je dois comparer 2 feuilles Excel. Je dois prendre la 1ère valeur dans la feuille 1 et la chercher dans la feuillle 2. Si je la trouve, je dois comparer d'autres colonnes pour m'assurer que c'est bien la bonne ligne. Si toutes les conditions sont remplis, je copie la ligne de la feuille 1 dans l'onglet "match". Si non, je copie cette ligne dans l'onglet "mismatch" avec un message indiquant quelle conditions n'a pas été remplie.
Je suis débutante et je pense qu'il y a une erreur de logique de ma boucle. Merci!