在多工作表中查找重复项的VBA函数实现需求

Hey there! Let's get that dual-worksheet duplicate finder up and running. Your single-sheet function works great, but the issue with your pseudocode is that you can't use & to combine two ranges—we need to use Excel's Union function to merge those two worksheet ranges into a single searchable area.

Option 1: Combine Ranges with Union (Cleanest Approach) #

This method merges the two target ranges first, then runs a single Find operation across both sheets:

Function FindDuplicate(factnr) As Boolean
    Dim combinedRange As Range
    Dim foundCell As Range
    
    ' Merge the D6:D206 ranges from both sheets into one range object
    Set combinedRange = Union( _
        Worksheets("Sheet 1").Range("D6:D206"), _
        Worksheets("Sheet 2").Range("D6:D206") _
    )
    
    ' Search the combined range for the target value
    Set foundCell = combinedRange.Find( _
        What:=factnr, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False _
    )
    
    ' Return True if found, False otherwise
    FindDuplicate = Not foundCell Is Nothing
End Function

Option 2: Step-by-Step Worksheet Check (Alternative) #

If you prefer to check one sheet at a time (which can be slightly faster if the value is often in the first sheet), use this version:

Function FindDuplicate(factnr) As Boolean
    Dim foundCell As Range
    
    ' Check Sheet 1 first
    With Worksheets("Sheet 1").Range("D6:D206")
        Set foundCell = .Find(factnr, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not foundCell Is Nothing Then
            FindDuplicate = True
            Exit Function ' No need to check Sheet 2 if we found it here
        End If
    End With
    
    ' Check Sheet 2 if Sheet 1 had no match
    With Worksheets("Sheet 2").Range("D6:D206")
        Set foundCell = .Find(factnr, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not foundCell Is Nothing Then
            FindDuplicate = True
            Exit Function
        End If
    End With
    
    ' Value not found in either sheet
    FindDuplicate = False
End Function

Why Your Pseudocode Didn't Work #

The line Worksheets("Sheet 1").Range("D6:D206") & ("Sheet 2").Range("D6:D206") uses the string concatenation operator (&), which tries to turn the ranges into text instead of merging them as range objects. Using Union is the correct way to combine multiple ranges for a single search.

内容的提问来源于stack exchange，提问作者Erwin Hermsen