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, Aug 06 '14 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, Aug 06 '14 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, Aug 06 '14 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, Aug 06 '14 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](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Aug 06 '14 at 17:51

score 3 · Accepted Answer · edited May 23 '17 at 12:20

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

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, Aug 06 '14 at 17:58

Application Defined error on Simple range

1 Answers1