CODEWITHSUNDEEP
vbaexcelexcel-2010
Paradox
Paradox

Reputation: 4556

VBA Run-time error 9 With Sheet

So here I have VBA code that is a part of my function, yet whenever I run it, I get the following error:

Run-Time error '9': Subscript out of range

The actual worksheet exists. In the vba editor on the side panel, it appears as Sheet2(Data_Sheet). In the details in that panel, it shows (Name) as being Sheet 11 and Name as being Data_Sheet. Dos anybody know a possible source of this error? My code is below:

With Sheets("Data_Sheet")

        'this searches just row 1
        Set header_cell_1 = .Rows(1).Find(What:="One", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_2 = .Rows(1).Find(What:="Two", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_3 = .Rows(1).Find(What:="Three", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_4 = .Rows(1).Find(What:="Four", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_5 = .Rows(1).Find(What:="Five", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_6 = .Rows(1).Find(What:="Six", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Set header_cell_7 = .Rows(1).Find(What:="Seven", lookat:=xlWhole, MatchCase:=False, searchformat:=False)

        col_1 = header_cell_1.Column
        col_2 = header_cell_2.Column
        col_3 = header_cell_3.Column
        col_4 = header_cell_4.Column
        col_5 = header_cell_5.Column
        col_6 = header_cell_6.Column
        col_7 = header_cell_7.Column

    End With

Upvotes: 0

Views: 4268

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12645

As from comments, the macro is running from the PERSONAL.XSLB workbook so it's trying to finding the Sheets("Data_sheet") in it, and clearly not finding because it is into another workbook --> Subscript out of range.

To fix, always use full references of the Object you work with: 

With Workbooks("myWorkbook.xlsm").Sheets("Data_sheet") '<-- explicitly saying in which workbook it must look for the sheet

End With

Alternatively, remember that:

  • ThisWorkbook references the workbook the code runs from. In your case, it would reference the PERSONAL.XLSB;
  • ActiveWorkbook references the currently active workbook. Very risky to use (usually, you should know which workbook you want to target). But there might be some cases in which you want the code to intentionally running on the active workbook (see the example of an add-in: the code runs from the add-in, but you want the add-in to run on the workbook from which is used).

WARNING:

A common error is not to use any reference, like in your case: 

With Sheets("Data_Sheet") '<-- of which workbook?

In that case, VBA answers the question itself with ActiveWorkbook, which is the default object. Only if you explicitely want to run from the ActiveWorkbook you should leave it like that; otherwise, always reference the object to avoid any bug related to this.

Upvotes: 4

Related Questions