使用WorksheetFunction.CountIf跨工作表对比触发1004错误求助

Let's break down what's triggering that 1004 error and fix your code step by step:

Key Issues in the Original Code #

• Misdefined & Unused Variables: You declared Sheet1 and Sheet2 as Sheets (a collection of sheets) instead of Worksheet, then set them to the entire Sheets collection—these variables aren't even used, so they're just cluttering your code.
• Unqualified Range References: When you use Range("A2").End(xlDown) inside the CountIf for Sheet2, you don't specify it belongs to Sheet2. VBA defaults to the active worksheet here, so if Sheet1 is active, you're trying to mix ranges from two different sheets in CountIf—this is invalid and causes the 1004 error.
• Broken Output Logic: Your code writes every "Yes"/"No" to the next empty row in Column C, regardless of which row in Column A it's checking. This stacks results at the bottom of Column C instead of aligning them with the rows you're validating.

Corrected VBA Code #

Sub sbWriteIntoCellData()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim checkRange As Range
    Dim rngCell As Range
    
    ' Explicitly set references to your target worksheets
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' Define the range to check in Sheet2 (fully qualified to avoid ambiguity)
    Set checkRange = ws2.Range("A2", ws2.Range("A2").End(xlDown))
    
    ' Loop through each cell in Sheet1's Column A (from A2 to last used row)
    For Each rngCell In ws1.Range("A2", ws1.Range("A2").End(xlDown))
        ' Use CountIf with the properly defined check range
        If WorksheetFunction.CountIf(checkRange, rngCell.Value) = 1 Then
            ' Write result to Column C of the same row as the checked cell
            ws1.Range("C" & rngCell.Row).Value = "Yes"
        Else
            ws1.Range("C" & rngCell.Row).Value = "No"
        End If
    Next rngCell
    
    MsgBox "Execution completed"
End Sub

What We Fixed & Improved #

1. Explicit Worksheet References: Using ws1 and ws2 removes any ambiguity about which worksheet a range belongs to, eliminating cross-range errors.
2. Fully Qualified Ranges: Every Range call is prefixed with its parent worksheet (e.g., ws2.Range("A2")), which fixes the 1004 error directly by ensuring VBA doesn't default to the active sheet.
3. Aligned Output: Results now appear in Column C on the same row as the corresponding value in Column A, making your data easy to cross-reference.
4. Cleaner Code: Removed unused variables and defined all necessary ones clearly, making the script easier to read and maintain.

If you still run into issues, double-check that:

• "Sheet1" and "Sheet2" exist in your workbook (spelling is case-insensitive but must match exactly)
• Column A in Sheet2 has data starting at A2 (if it's empty, End(xlDown) will jump to the last row of the sheet—add a check for empty ranges if needed)

内容的提问来源于stack exchange，提问作者srawan kallakuri