Sub chrise()
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Application.ScreenUpdating = False
For Each c In ws1.Columns(4).SpecialCells(2)
Set d = ws1.Range(c.Offset(1, 0), ws1.Cells(Rows.Count, 4).End(3)).Find(c.Value)
If Not d Is Nothing Then
Set e = ws2.Columns(1).Find(d.Value)
If Not e Is Nothing Then
e.Offset(0, 1).Value = e.Offset(0, 1).Value + 1
Else
Set nr = ws2.Cells(Rows.Count, 1).End(3).Offset(1, 0)
nr.Value = d.Value
nr.Offset(0, 1).Value = 2
End If
End If
Next
ws2.Sort.SortFields.Clear
ws2.Sort.SortFields.Add Key:=Range("B2:B" & nr.Row), Order:=xlDescending
With ws2.Sort
.SetRange Range("A2:" & nr.Offset(0, 1).Address)
.Apply
End With
Application.ScreenUpdating = True
End Sub
Bookmarks