0

I keep getting the Error 1004: Application Defined or Object Defined

For these lines of code: 

Input formulas for Summary Worksheet
    Worksheets("Summary " & q).Select
    Dim CountRows As Integer
    CountRows = Application.CountA(Worksheets("Transactions").Range("A:A"))
    AtTimes = 22 + q

    Worksheets("Transactions").Cells.EntireColumn.AutoFit
    Worksheets("Summary " & q).Cells.EntireColumn.AutoFit

    For x = 2 To h Step 1
        Worksheets("Summary " & q).Cells(3, x).Value = WorksheetFunction.Sum(WorksheetFunction.SumIfs(Worksheets("Transactions").Range("S:S"), _
        Worksheets("Transactions").Range("C:C"), Worksheets("Summary " & q).Cells(2, x), _
        Worksheets("Transactions").Range("T:T"), "False", _
        Worksheets("Transactions").Range(Cells(2, AtTimes), Cells(CountRows, AtTimes)), "True", _
        Worksheets("Transactions").Range("F:F"), "Transfer In", _
        Worksheets("Transactions").Range("F:F"), "Cash Deposit"))
    Next x

I tried to debug by using the "Watch" function in VBA, all variables did not even change when the error 1004 pops up, Which I think it means the macro stops as soon as it enters this loop.

Why and how can I fix this problem?

Any help is much appreciated! Time is also of the essence because this project is due soon for my boss to see :)

JanLeeYu
  • 981
  • 2
  • 9
  • 24
Ethan Tang
  • 1
  • Beyond the orphaned reference mentioned below, you cannot have a [SUMIFS function](https://support.office.com/en-us/article/SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B) with different range sizes. `SUMIFS(A:A, Z1:Z99, True, B:B , False)` is wrong. It should be `SUMIFS(A:A, Z:Z, True, B:B , False)` –  Mar 14 '16 at 06:43

1 Answers1

0

This:

Worksheets("Transactions").Range(Cells(2, AtTimes), Cells(CountRows, AtTimes))

Will cause a 1004 error if Transactions is not the active sheet, because Cells() with no qualifying worksheets refers to the active sheet, not Transactions

Tim Williams
  • 154,628
  • 8
  • 97
  • 125