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
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