Interpreting VB code in Excel Macro

[Richard Webb]

Can someone tell me what this code means? I am trying to use it to compare 2 worksheets. Want to know how they are alike and how they are different. The code was obtained from 'Experts Exchange'. It works, I just don't understand how. The gentleman from 'Experts Exchange' explaination was too technical or I am too hardheaded (probably the latter). If anyone would care to try to explain I will appreciate it.



Sub mPartNumber()
Static bFound
Dim c As Range, mlRange As Range
'I hope this is not read but is a comment
For Each c In Sheets("PI").Range("A2:A" & Sheets("PI").Range("A65536").End(xlUp).Row).Cells
If Application.WorksheetFunction.CountIf(Sheets("ML").Range _
("B2:B" & Sheets("PI").Range("B65536").End(xlUp).Row), c.Value) > 0 Then
c.EntireRow.Copy Destination:=Worksheets("PI=ML").Rows _
(Sheets("PI=ML").Range("A65536").End(xlUp).Row + 1)
bFound = True
End If
Next c
For Each c In Sheets("ML").Range("B2:B" & Sheets("ML").Range("B65536").End(xlUp).Row).Cells
If Application.WorksheetFunction.CountIf(Sheets("PI").Range("A2:A" & Sheets("PI") _
.Range("A65536").End(xlUp).Row), c.Value) = 0 Then
c.EntireRow.Copy Destination:=Worksheets("PI not=ML").Rows _
(Sheets("PI not=ML").Range("B65536").End(xlUp).Row + 1)
End If
Next c
End Sub

[mudraker]

This part of the code finds last used row sheet PI column A
Sheets("PI").Range("A65536").End(xlUp).Row).Cells


this part of the code finds the next vacant row in sheet PI = ML
Sheets("PI=ML").Range("A65536").End(xlUp).Row + 1



This part of code loops through all sheet PI column A cells 1 to last used row

For Each c In Sheets("PI").Range("A2:A" & Sheets("PI").Range("A65536").End(xlUp).Row).Cells

this part of code uses Excels countif function to determine if cell value that is refered to in c of the For loop appears in sheet ML if it does then it copies that row from Sheet PI to sheet PI = ML

If Application.WorksheetFunction.CountIf(Sheets("ML").Range _
("B2:B" & Sheets("PI").Range("B65536").End(xlUp).Row), c.Value) > 0 Then

This part copies the row to a vacant row to a different sheet
c.EntireRow.Copy Destination:=Worksheets("PI=ML").Rows _
(Sheets("PI=ML").Range("A65536").End(xlUp).Row + 1)
bFound = True
End If
this is the end of the loop
Next c


This part is similar to the 1st for loop but checks if sheet ML column A is not in sheet PI. If it is not then it copies the row from ML to PI not in ML



For Each c In Sheets("ML").Range("B2:B" & Sheets("ML").Range("B65536").End(xlUp).Row).Cells
If Application.WorksheetFunction.CountIf(Sheets("PI").Range("A2:A" & Sheets("PI") _
.Range("A65536").End(xlUp).Row), c.Value) = 0 Then
c.EntireRow.Copy Destination:=Worksheets("PI not=ML").Rows _
(Sheets("PI not=ML").Range("B65536").End(xlUp).Row + 1)
End If
Next c
End Sub


I hope this helps you