Excel VBA Macro with run-time error '1004'

Question

can anyone help me with this? for this particular function, it is getting run-time error 1004 for "sheet1.Range("a1:a" & RowNbr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sheet1.Range("J1"), Unique:=True"

this is the full codes:

Sub stock_exercise()
Dim RowNbr As Long
Dim uniqueticker As Long
Dim totalvolume As String
Dim r As Long
Dim sheet1 As Worksheet

Set sheet1 = ThisWorkbook.Sheets("A")'determine row count
RowNbr = sheet1.Range("A1", sheet1.Range("A1").End(xlDown)).Rows.Count

'copy and past unique ticker from column a to column j
sheet1.Range("a1:a" & RowNbr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sheet1.Range("J1"), Unique:=True

sheet1.Range("J1").Value = "ticker"
uniqueticker = sheet1.Range("j1", Range("j1").End(xlDown)).Rows.Count

'sumif of 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

Answer 1

Try to clear contents of target cell where advanced filter values are being copied and see if it works.

Sub stock_exercise()
Dim RowNbr As Long
Dim uniqueticker As Long
Dim totalvolume As String
Dim r As Long
Dim wksOne As Worksheet

Set wksOne = ThisWorkbook.Sheets("A") 'determine row count
RowNbr = wksOne.Range("A1", wksOne.Range("A1").End(xlDown)).Rows.Count

'clear contents of destination cell before running the code
wksOne.Range("J1").CurrentRegion.ClearContents

'copy and past unique ticker from column a to column j
wksOne.Range("a1:a" & RowNbr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wksOne.Range("J1"), Unique:=True

wksOne.Range("J1").Value = "ticker"
uniqueticker = wksOne.Range("j1", Range("j1").End(xlDown)).Rows.Count

'sumif of each ticker
For r = 2 To uniqueticker
    totalvolume = Application.SumIf(wksOne.Range("a1:a" & RowNbr), wksOne.Cells(r, 10), wksOne.Range("g1:g" & RowNbr))
    wksOne.Cells(r, 11).Value = totalvolume
Next r
End Sub

Answer 2

When it fails, choose Debug and view the "Immediate Window". The immediate window, type ?RowNbr and press Enter. You'll see the value of RowNbr... it may be null (causing the error). If it is, you can work back up the code in the Immediate window, e.g. pasting in each line in turn with a ? in front. You can check whether the range is what you expect, the sheet and even the workbook, e.g. ?sheet1.name and ?thisworkbook.name.

You can also paste in segments of each line, to check their value is as expected, e.g. ?sheet1.Range("a1:a" & RowNbr).address

Answer 3

Did a little code cleanup and the code below works by me. I only tested with empty criteria ranges, which means column K (col 11 in the code) is populated with zero values, so if the run-time error 1004 reappears by you with this code, it suggest the problem lies here. In that case, please make sure all the values in the criteria ranges of the SumIf function are valid.

Sub stock_exercise()
    Dim r As Long, RowNbr As Long
    Dim uniqueticker As Long
    Dim rng As Range
    Dim sheet1 As Worksheet

    Application.ScreenUpdating = False

    Set sheet1 = ThisWorkbook.Sheets("A")

    With sheet1

        'determine row count
        RowNbr = .Range("A1", .Range("A1").End(xlDown)).Rows.Count

        Set rng = .Range("a1:a" & RowNbr)

        'copy and paste unique ticker from column a to column j
        rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("J2"), Unique:=True

        .Range("J1").Value = "ticker"
        uniqueticker = .Range("j1", Range("j1").End(xlDown)).Rows.Count - 1 ' minus one, since we insert from row two

        'sumif of each ticker
        For r = 2 To uniqueticker + 1

            ' insert total volume
            .Cells(r, 11).Value = Application.SumIf(rng, .Cells(r, 10), .Range("g1:g" & RowNbr))
        Next r

    End With

    Application.ScreenUpdating = True

End Sub