Run-time Error: 1004 Application-defined or Object-defined error

Question

I'm getting a run-time error when I run the following code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = False
Application.EnableEvents = False

Dim c, OrgList, vtList, FndRng As Range
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim Org, Track As Worksheet

Set Org = ActiveWorkbook.Worksheets("Org List")
Set Track = ActiveWorkbook.Worksheets("Visit Tracking Sheet")
Set Tbl = Track.ListObjects("TrackTbl")
Set OrgList = Org.Range(Cells(2, 1), Cells(Org.UsedRange.Rows.Count, 1))
Set vtList = Track.Range("TrackTbl[Org ID]")

Everything runs fine until I get to Set ChngList = Track.Range("TrackTbl[Org ID]") which throws the Run-time Error: 1004. I have tried also the following replacement lines, all of which throw the same error:

Set vtList = Tbl.Range(Cells(2, 1), Cells(ListRows.Count, 1))
Set vtList = Track.Range(Cells(2, 1), Cells(Track.UsedRange.Rows.Count, 1))
Set vtList = Track.Tbl.Range(Cells(2, 1), Cells(ListRows.Count, 1))
Set vtList = Tbl.Range

I'm at a loss on this one and have had trouble finding a solution for this. I assume it is an issue with setting the Range referencing cells in a Table but am not sure.

Where is the rest of your code? I don't see where you define `ChngList` — dwirony, Jun 21 '17 at 19:46
Please see if [this](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) answers your question — Siddharth Rout, Jun 21 '17 at 19:52
@dwirony Sorry, that is supposed to say `Set vtList` as it is shown in the last line of the code I posted. — Will Clein, Jun 22 '17 at 12:33
@SiddharthRout this pointed me in the right direction. I will post the solution momentarily. — Will Clein, Jun 22 '17 at 12:59

score 1 · Answer 1 · answered Jun 22 '17 at 13:04

Thanks to Siddharth Rout for pointing my in the right direction for this one.

The answer involves two issues that I was not noticing. The first is that the error was actually occurring at the Set OrgList line instead of the Set vtList line. The error was being caused because even though I referenced the correct sheet, without a With line, I have to reference that sheet at every point in that line. The corrected code is below:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = False
Application.EnableEvents = False

Dim c, OrgList, vtList, FndRng As Range
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim Org, Track As Worksheet

Set Org = ActiveWorkbook.Worksheets("Org List")
Set Track = ActiveWorkbook.Worksheets("Visit Tracking Sheet")
Set Tbl = Track.ListObjects("TrackTbl")
Set OrgList = Org.Range(Org.Cells(2, 1), Org.Cells(Org.UsedRange.Rows.Count, 1))
Set vtList = Track.Range("TrackTbl[Org ID]")

I could have used With to eliminate the need to reference the sheet for each Range.Cells in the line, but it would have been the same amount of code and the sheet name is short. Either way should work for anyone running into this problem.

Run-time Error: 1004 Application-defined or Object-defined error

1 Answers1