CODEWITHSUNDEEP
excelvba
user9078057
user9078057

Reputation: 269

Setting Workbook object I get error '9': subscript out of range

I want to copy information from cells in M79to PAlysis.

My Sub PopulateFields is located in PAlysis.

What is wrong with my reference to a different file?

Sub PopulateFields()
    Dim Mur As Workbook, TOMS As Workbook, i As Integer, LastRow As Integer, j As Integer

    Set Mur = Workbooks("S:\M\ BPM\M79.xls")
    Set TOMS = Workbooks("S:\M\BPM\PAlysis.xlsm")
    Set TOMSPos = TOMS.Worksheets("Positions")
    Set TOMSAna = TOMS.Worksheets("Analysis")
    Set MurexWs = Murex.Worksheets("BB_Overview")

    LastRow = Murex.Cells(MurexWs.Rows.Count, 1).End(xlUp).Row

    j = 3
    For i = 3 To LastRow - 1
        If Mur.MurexWs.Cells(i, 2).Value = "Bond" Then
            Mur.MurexWs.Cells(j, 6).Copy TOMS.TOMSPos.Cells(i + 1, 1)
            j = j + 1
        Else
            j = j + 2
        End If
    Next i
 End Sub

In the lineSet Mur = ... I get

Error 9: Subscript out of range.

Upvotes: 0

Views: 59

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You could use the following to either get an already opened workbook, or open it if it is not opened.

Sub test()   
    Set mur = GetOrOpenWorkbook("S:\M\BPM\", "M79.xls")
    Set toms = GetOrOpenWorkbook("S:\M\BPM\", "PAlysis.xlsm")
End Sub

Public Function GetOrOpenWorkbook(Path As String, Filename As String) As Workbook
    'test if workbook is open
    On Error Resume Next
    Set GetOrOpenWorkbook = Workbooks(Filename)
    On Error GoTo 0

    'if not try to open it
    If GetOrOpenWorkbook Is Nothing Then
        Set GetOrOpenWorkbook = Workbooks.Open(Filename:=Path & Filename)
    End If
End Function

Upvotes: 1

FunThomas
FunThomas

Reputation: 29286

I assume that you want to open the workbooks: You have to use Workbooks.open. This opens a workbook in Excel (basically the same as opening it via File->Open in Excel)

Set Mur = Workbooks.open("S:\M\ BPM\M79.xls")

(not sure about the space before BPM - check if this is a typo.

If your workbook is already open, the command would be

Set Mur = Workbooks("M79.xls")

This is the syntax for VBA Collections where you can access an object either by (numeric) index or via it's name. The name of a workbook within the Workbooks-collection is the filename, but without the path (this is the reason that you cannot open 2 workbooks with the same name, even if they are stored in different folders).
When you try to access a member of a collection that doesn't exist, VBA will throw the Runtime Error 9.

Upvotes: 0

Related Questions