Application Defined error on Simple range

Question

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

You need to put Summary_File.Sheets("Settings") before each call to Cells. If you just put Cells, Excel interprets that as cells in the active sheet, even if it's in the arguments to a Range function that's correctly-scoped. — Chel
– Chel, Commented Aug 6, 2014 at 17:30
There are no parenthesis when assigning range to array change it to Field_List=Summary_File.Sheets("Settings").Range(Cells(2, 1), Cells(Last_Field, 1)) — Ravi Yenugu
– Ravi Yenugu, Commented Aug 6, 2014 at 17:34
Thanks for the input guys this looks like progress, but a new error comes up after including both of yours solutions RunTime Error 13: Tyep Mismatch — sgp667
– sgp667, Commented Aug 6, 2014 at 17:48
You are getting type mismatch because you didn't add .Value. See the answer that I posted. You may have to refresh the page — Siddharth Rout
– Siddharth Rout, Commented Aug 6, 2014 at 17:49
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 — Siddharth Rout
– Siddharth Rout, Commented Aug 6, 2014 at 17:51

Community · Accepted Answer · 2017-05-23 12:20:50Z

3

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Aug 6, 2014 at 17:45

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgp667 Over a year ago

I wish it was that simple, In comments rdhs posted similar solution, but I copy and Pasted yours, and it made progress I guess because I am getting "RunTime Error 13: Type Mismatch". So its not THAT simple but defienetly thanks.

sgp667 Over a year ago

Excuse me that was a Mistake. Thank you very much :)

Collectives™ on Stack Overflow

Application Defined error on Simple range

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related