CODEWITHSUNDEEP
vbaexcel
sgp667
sgp667

Reputation: 1875

Application Defined error on Simple range

I am trying to debug this code for quite some time already does anyone know what is the problem in it?

Code:

Sub Find_Field_List()
Dim Last_Field As Integer
Dim Field_List() As Variant
Last_Field = Summary_File.Sheets("Settings").Cells(1, 1).End(xlDown).Row
Field_List() = Summary_File.Sheets("Settings").Range(Cells(2, 1), Cells(Last_Field, 1))
End Sub

Error(highlights line starting with Field_List()):

RunTime Error 1004
Application-Defined or Object-Defined Error

Immediate Window:

?Summary_file.Sheets(2).Name
Settings
Split MBSA.xlsm
?Range(Cells(2,1),Cells(5,1)).Count
4 
?Last_Field
5
?Summary_File.Sheets(2).Range(Cells(1,1))
Fields

Upvotes: 1

Views: 91

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The reason is very simple in my opinion. The error with your code is that the cells Cells(2, 1) and Cells(Last_Field, 1) are not fully qualified. And you are getting this error because Summary_File.Sheets("Settings") is not active when the code is running. And hence one should always fully qualify the objects.

Try this. Notice the DOT before them in the code below.

Sub Find_Field_List()
    Dim Last_Field As Integer
    Dim Field_List() As Variant

    With Summary_File.Sheets("Settings")
        Last_Field = .Cells(1, 1).End(xlDown).Row
        Field_List = .Range(.Cells(2, 1), .Cells(Last_Field, 1)).Value
    End With
End Sub

EDIT:

One more tip: Try and avoid the use of .End(xlDown).Row You may end up selecting the entire column! If you want to select only till the last row then you may want to see THIS

Upvotes: 3

Related Questions