Minuette said:
I need to compare a new worksheet to a very large master worksheet and
generate a list of names which are missing from the master worksheet. Is
there a function that will help me do this?
Here is one, supply your ranges in Definitions (initial cells only):
=========================
Sub ComparingData()
Dim CompareColl As New Collection
Dim MissingFromMaster As New Collection
Dim SourceRange As Range
Dim CompareToRange As Range
Dim TargetRange As Range, i
' Definitions
' --------------------------------------------------
Set SourceRange = [Sheet10!W70]
Set CompareToRange = [NameSheet!I32]
Set TargetRange = [Sheet10!X70]
'---------------------------------------------------
Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
Set CompareToRange = Range(CompareToRange, CompareToRange.End(xlDown))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each i In SourceRange
On Error Resume Next
CompareColl.Add i, i
Next
For Each i In CompareToRange
On Error GoTo MissingName
CompareColl.Add i, i
On Error Resume Next
MissingFromMaster.Add i, i
Continue:
Next
On Error GoTo 0
For i = 1 To MissingFromMaster.Count
TargetRange(i) = MissingFromMaster(i)
Next
Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
MissingName:
Resume Continue
End Sub
===========================
Let me know how things go.
Ciao, Bruno
