Excel VBA宏运行时错误'1004'求助：AdvancedFilter语句报错

Hey there! Let's break down why that Runtime Error 1004 is hitting your AdvancedFilter line, and get your macro working as intended to pull unique tickers and calculate total volumes.

Common Causes of the Error & Fixes #

Let's go through the key issues in your code one by one:

1. Unreliable Last Row Calculation
   Your current way of getting RowNbr can fail if column A only has a header (no data) — Range("A1").End(xlDown) will jump to the very bottom of the sheet, creating a massive invalid range. Instead, use this more robust method to find the last used row in column A:

   RowNbr = sheet1.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
   

2. AdvancedFilter Target Range Conflicts
   When using AdvancedFilter with Action:=xlFilterCopy, the CopyToRange needs to either be empty or match the source header. You were setting J1 to "ticker" after running the filter, which could cause conflicts if J1 had existing data. Instead:

   • Clear the target range (J:K) first to avoid old data interfering
   • Make sure the source header (A1) matches the target header we set before running the filter

3. Unqualified Range References
   In the line calculating uniqueticker, you used Range("j1").End(xlDown) without specifying sheet1 — if another sheet is active, this will pull data from the wrong sheet. Always qualify your ranges with the worksheet object.

Corrected Full Code #

Here's the revised macro with all fixes applied:

Sub stock_exercise()
    Dim RowNbr As Long
    Dim uniqueticker As Long
    Dim totalvolume As Double ' Changed to Double since volume is a number, not string
    Dim r As Long
    Dim sheet1 As Worksheet
    
    Set sheet1 = ThisWorkbook.Sheets("A")
    
    ' Get last used row in column A (robust method)
    RowNbr = sheet1.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
    
    ' Clear target columns J and K to avoid old data conflicts
    sheet1.Range("J:K").ClearContents
    ' Set target header first to match source
    sheet1.Range("J1").Value = "ticker"
    
    ' Run AdvancedFilter with proper qualified ranges
    sheet1.Range("A1:A" & RowNbr).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=sheet1.Range("J1"), _
        Unique:=True
    
    ' Get count of unique tickers (qualified range)
    uniqueticker = sheet1.Cells(sheet1.Rows.Count, "J").End(xlUp).Row
    
    ' Calculate total volume for each ticker
    For r = 2 To uniqueticker
        totalvolume = Application.SumIf( _
            sheet1.Range("A1:A" & RowNbr), _
            sheet1.Cells(r, 10), _
            sheet1.Range("G1:G" & RowNbr) _
        )
        sheet1.Cells(r, 11).Value = totalvolume
    Next r
End Sub

Extra Notes #

• Changed totalvolume from String to Double because volume is a numeric value — storing it as a string can cause issues with calculations or formatting later.
• Added Range("J:K").ClearContents to ensure old results don't interfere with new runs.
• Used sheet1.Cells(sheet1.Rows.Count, "J").End(xlUp).Row to get the last row of unique tickers, which is more reliable than relying on End(xlDown) (which stops at the first blank cell).

内容的提问来源于stack exchange，提问作者Andres Silvia