Error '1004'：无法获取WorksheetFunction类Sum属性问题求助

Hey Mike, let's break down why you're hitting this 1004 error only on some worksheets—this is super common when dealing with WorksheetFunction.Sum and ranges in loops, so let's walk through the most likely culprits:

• Empty or Non-Numeric Ranges
  The WorksheetFunction.Sum will throw this error if the range you're trying to sum is either completely empty, or contains no numeric values (all text, errors like #N/A/#VALUE!, etc.). Since it works on some sheets, check if the Rng2 or WorkRng2 ranges on the failing sheets have:

  • Blank cells across the entire range
  • Cells with non-numeric data that can't be summed
    Pro tip: Swap WorksheetFunction.Sum with Application.Sum temporarily—this won't throw an error, it'll just return 0 for empty/non-numeric ranges, which can help you quickly identify which ranges are causing the issue.

• Unqualified Range References in Loops
  Since this is happening in a loop, it's easy to accidentally create broken range references. Double-check how Rng2 is being set on each iteration:

  • Always explicitly qualify the worksheet for Rng2 and WorkRng2. For example, instead of Set Rng2 = Range("A1:A10"), use Set Rng2 = ws.Range("A1:A10") where ws is the current worksheet object in your loop. Without this, Excel might be referencing the wrong sheet's range.
  • Verify that the range addresses you're using actually exist on each worksheet (some sheets might have fewer rows/columns than others, leading to invalid references).

• Hidden or Protected Worksheets
  WorksheetFunction.Sum can fail if the target sheet is hidden or protected:

  • Check if the failing sheets are hidden (even "very hidden")—unhide them temporarily to test if the error goes away.
  • If the sheet is password-protected, you'll need to unprotect it before running the sum operation, then re-protect it afterward.

• Merge Cells in the Target Range
  Merged cells often mess with range-based functions because they don't behave like regular single-cell ranges. If Rng2 or WorkRng2 includes merged cells, try unmerging them temporarily to see if that resolves the error.

Let me know if you test these and still run into issues—we can dig deeper into your loop code if needed!

内容的提问来源于stack exchange，提问作者Mike Mann