Consulting

Results 1 to 4 of 4

Thread: Cannot Find Source of Error 9

  1. #1
    VBAX Regular
    Joined
    Oct 2013
    Posts
    7
    Location

    Cannot Find Source of Error 9

    Working in an "invoice" workbook, all I'm trying to do is get 5 pieces of information from another open workbook (SHMaster), InvNo from sheet Inv Data and Bill to data from sheet Cust Data. Cannot get past the error 9 and there is no line highlighted. I've checked and rechecked the file name and path. Can anyone see something I don't? Thanks all.

    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim awf As Object
    Dim name As String
    If Range("InvNo") = "" Then
        Set wks1 = Workbooks("c:\SH\SHMaster.xlxm").Sheets("Cust Data")
        Set wks2 = Workbooks("c:\SH\SHMaster.xlxm").Sheets("Inv Data")   '
        Set awf = Application.WorksheetFunction
            
        Range("InvNo") = wks2.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value
        name = wks1.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Value
        Range("InvBillName") = awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 1)
        Range("InvBillAdr1") = awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 2)
        Range("InvBillAdr2") = awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 3)
        Range("InvBillCSZ") = awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 4) & " " & _
            awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 5) & "  " & _
            awf.Index(wks1.Range("arrCustData"), awf.Match(name, wks1.Range("lstCustName"), False), 6)
    End If
    End Sub
    Last edited by Aussiebear; 10-22-2013 at 11:54 AM. Reason: added code tags

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Error # 9 = Referenced Workbook not open.

    You can find the line that errors by placing the cursor inside the sub and pressing F8 to step thru the sub line by line.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Error 9 = Subscript

    It's probably because you have

    Workbooks("c:\SH\SHMaster.xlxm").Sheets("Cust Data")
    (the .xlxm extension)

    instead of

    Workbooks("c:\SH\SHMaster.xlsx").Sheets("Cust Data")
    
    or
    
    Workbooks("c:\SH\SHMaster.xlsm").Sheets("Cust Data")

    Paul

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    You also don't use the path in the index:
    [vba]
    Set wks1 = Workbooks("SHMaster.xlsm").Sheets("Cust Data")
    Set wks2 = Workbooks("SHMaster.xlsm").Sheets("Inv Data")[/vba]
    assuming the workbook is open, as mentioned, otherwise you have to open it first.
    Be as you wish to seem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •