Worksheets reconciliation

[Apple08]

Hi All

I have to reconcile the data for two worksheets 'Master' and 'Change Log'. If the cells in the columns A, B and F of the Change Log equal to Master cells value in columns A, B, F, then it has to reconcile the rest of cells in columns C, D, G, H, I and J . If the cells in any columns C, D, G, H, I and J of the Master don't equal to the Change log cell value, then Master cell value will have to be replaced by the Change log cell value, and the Change log cell will be highlighted in yellow.

I have drafted the macro below, however there is an error message 'Compile error: Invalid Next control variable reference' and the error highlighted the 'Next i'

Please could any one advise, many thanks.

[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Reconcile()

Dim ws1 As Worksheet
  Dim LastRow As Long
  Dim LRow As Long
  Dim i As Long
  Dim r As Long

    Set ws = Worksheets("Master")
    Set ws1 = Worksheets("Change Log")
  

LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 2 Step -1
For r = LastRow To 2 Step -1

    If ws1.Cells(i, "A").Value = ws.Cells(r, "A").Value And ws1.Cells(i, "B").Value = ws.Cells(r, "B").Value And ws1.Cells(i, "F").Value = ws.Cells(r, "F").Value Then
        If ws1.Cells(i, "C").Value <> ws.Cells(r, "C").Value Then
            ws1.Cells(i, "C").Interior.ColorIndex = 6 And ws.Cells(i, "C").Value = ws1.Cells(i, "C").Value
      
        If ws1.Cells(i, "D").Value <> ws.Cells(r, "D").Value Then
            ws1.Cells(i, "D").Interior.ColorIndex = 6 And ws.Cells(i, "D").Value = ws1.Cells(i, "D").Value
                  
        If ws1.Cells(i, "G").Value <> ws.Cells(r, "G").Value Then
            ws1.Cells(i, "G").Interior.ColorIndex = 6 And ws.Cells(i, "G").Value = ws1.Cells(i, "G").Value
      
        If ws1.Cells(i, "H").Value <> ws.Cells(r, "H").Value Then
            ws1.Cells(i, "H").Interior.ColorIndex = 6 And ws.Cells(i, "H").Value = ws1.Cells(i, "H").Value
      
        If ws1.Cells(i, "I").Value <> ws.Cells(r, "I").Value Then
            ws1.Cells(i, "I").Interior.ColorIndex = 6 And ws.Cells(i, "I").Value = ws1.Cells(i, "I").Value
          
        If ws1.Cells(i, "J").Value <> ws.Cells(r, "J").Value Then
            ws1.Cells(i, "J").Interior.ColorIndex = 6 And ws.Cells(i, "J").Value = ws1.Cells(i, "J").Value
          
       End If
       End If
       End If
       End If
       End If
       End If
     
     End If
  
Next i
Next r

End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[mse330]

Try to switch the order of Next i and Next r since you have your For r before your For i loop

 

[Apple08]

Thanks. I have switched the Next I and Next r. No more error message, however there is nothing happened in the two worksheets. Data hasn't been updated in the Master, and also no highlight in the Change Log.

Is my macro wrong?

 

[mse330]

After the "Then" you are putting your first required change then "And" the 2nd required change whereas it should be in a different line or separated by a colon instead of "And"

Also, you could make your code a little shorter by a third loop instead of repetitive If conditions like below

Sub Reconcile_V2()
Dim ws1 As Worksheet, LastRow As Long, LRow As Long, i As Long, r As Long
Set ws = Worksheets("Master")
Set ws1 = Worksheets("Change Log")
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
For r = LastRow To 2 Step -1
If ws1.Cells(i, "A").Value = ws.Cells(r, "A").Value _
And ws1.Cells(i, "B").Value = ws.Cells(r, "B").Value _
And ws1.Cells(i, "F").Value = ws.Cells(r, "F").Value Then
For Each Z In Array("C", "D", "G", "H", "I", "J")
If ws1.Cells(i, Z).Value <> ws.Cells(r, Z).Value Then
ws1.Cells(i, Z).Interior.ColorIndex = 6
ws.Cells(i, Z).Value = ws1.Cells(i, Z).Value
End If
Next Z
End If
Next r
Next i
End Sub

 

[Apple08]

Many thanks! I have modified
ws.Cells(i, Z).Value = ws1.Cells(i, Z).Value to
ws.Cells(r, Z).Value = ws1.Cells(i, Z).Value,

then it works perfectly! It was my mistake in my first set of code.



Many thanks mse330! I appreciated it very much! It's so nice to have clever people to help when you got stuck! Cheers!

 

[mse330]

Glad to help & thanks for reporting back

 

[Apple08]

Hiya, I realised some of the cells in

ws1.Cells(i, Z).Value is blank, if it is blank ws.cells(I, Z). value should remain as it is rather than becomes blank.

I struggle how to update the code and please advise.​

 

[mse330]

Try changing the below line

If ws1.Cells(i, Z).Value <> ws.Cells(r, Z).Value And IsEmpty(ws1.Cells(i, Z)) = False Then

 

[Apple08]

Thank you so much!