Previous Topic Next Topic

Compare two worksheets macro

Closed
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011 - Dec 29, 2010 at 11:34 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 9, 2011 at 04:57 PM
Hello,
I was looking for some help as far as setting up a macro that compares to worksheets into a third worksheet and came across this macro. Thank you to the person who came up with this macro.

Sub Compare()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("Confirmed").Cells.Clear
With Worksheets("testing")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sourcedata")
Set cfind = .Columns("A:A").Cells.Find _
(what:=c.Value, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
c.EntireRow.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'c.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'c.Offset(0, 2).Copy Worksheets("Confirmed").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)


End With 'sourcedata
line1:
Next c
Application.CutCopyMode = False


I am new to macros and this looks like it will resolve my issue. Can someone please explain to me what the code does , i.e. the process in which the macro compares the two worksheets.

Thank You,

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Feb 9, 2011 at 04:57 PM 
Sub Compare()
   
   'declaration of three variable of type range
   Dim rng As Range, c As Range, cfind As Range
   
   'in case of error just execute the next line
   On Error Resume Next
   
   'clear all the cells on the worksheet named "Confirmed"
   Worksheets("Confirmed").Cells.Clear
   
   ' a short cut , so that one does not have to write Worksheets("testing") every time
   With Worksheets("testing")
      
      'in the line below .Range("a2").End(xlDown) will find the first empty cell after A2 and give u
      ' one cell above it. HOWEVER If all cells after A2 are blank, then it means it will give you the
      ' last cell of the column
      'set the variable rng to be equal to range A2 till the last cell as determined by .Range("a2").End(xlDown)
      Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
      
         ' looping true each cells in the range in the variable rng
         ' c will hold the value of the cell
         For Each c In rng
         
            ' now refering to Worksheets("sourcedata") for shortcut
            With Worksheets("sourcedata")
            
               'find in column A or sourcedata sheet, the value of the cell being looped.
               'xlWhole is saying the match needs to be complete (though not case sensitive)
               Set cfind = .Columns("A:A").Cells.Find _
                                       (what:=c.Value, lookat:=xlWhole)
                                       
               ' if no match was found, then goto label  "line1"
               If cfind Is Nothing Then GoTo line1
               
               'copy the entire row of the cell being looped and paste on Worksheets("Confirmed")
               ' on the first blank line from the bottom
               c.EntireRow.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
               
               ' this line has been commented out
               'c.Copy Worksheets("Confirmed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
               
               ' this line has been commented out
               'c.Offset(0, 2).Copy Worksheets("Confirmed").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            
            ' end of short cut for sourcedata
            End With 'sourcedata
            
'a label named line1
line1:
   
      ' loop to next cell in range rng
      Next c
      
      'clear out the clipboard to free up memory
      Application.CutCopyMode = False
      
   'end of short cut for "testing"
   End With '"testing"
End Sub
0