PDA

View Full Version : Cannot Find Source of Error 9



CdyMan
10-22-2013, 07:43 AM
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

SamT
10-22-2013, 01:25 PM
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.

Paul_Hossler
10-23-2013, 06:42 PM
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

Aflatoon
10-24-2013, 01:51 AM
You also don't use the path in the index:

Set wks1 = Workbooks("SHMaster.xlsm").Sheets("Cust Data")
Set wks2 = Workbooks("SHMaster.xlsm").Sheets("Inv Data")
assuming the workbook is open, as mentioned, otherwise you have to open it first.