我正在尝试从excel中的客户数据列表中创建自动发票。我在宏和一些VBA方面有经验,但之前没有做过深入的介绍。我在此处复制所需的基本模板。我复制了大部分结构并更改了所需的内容,但它对我不起作用。我尝试自己研究它并研究其他方法,但现在恐怕不知所措。这是我的代码,出现“运行时错误1004”。如果有人可以告诉我需要什么来解决它,我将不胜感激。
代码:
Private Sub CommandButton1_Click()
Dim customer As String
Dim customerid As String
Dim invoicenumber As Long
Dim r As Integer
Dim mydate As String
Dim path As String
Dim myfilename As String
Dim providercount As Integer
Dim basefee As Integer
Dim faxlines As Integer
Dim faxpages As Integer
Dim faxbundles As Integer
Dim invoicedate As String
Dim lastrow As Long
'This row is causing the error
lastrow = Sheets("Greenway").Range(“A” & Rows.Count).End(xlUp).Row
r = 8
For r = 8 To lastrow
If Cells(r, 14).Value = “done” Then GoTo nextrow
customer = ThisWorkbook.Sheets(“Greenway”).Cells(r, 3).Value
customerid = ThisWorkbook.Sheets(“Greenway”).Cells(r, 2).Value
providercount = ThisWorkbook.Sheets(“Greenway”).Cells(r, 5).Value
basefee = ThisWorkbook.Sheets(“Greenway”).Cells(r, 6).Value
faxlines = ThisWorkbook.Sheets(“Greenway”).Cells(r, 7).Value
faxpages = ThisWorkbook.Sheets(“Greenway”).Cells(r, 10).Value
faxbundles = ThisWorkbook.Sheets(“Greenway”).Cells(r, 11).Value
invoicenumber = ThisWorkbook.Sheets(“Greenway”).Cells(r, 15).Value
invoicedate = ThisWorkbook.Sheets(“Greenway”).Cells(r, 16).Value
Cells(r, 14).Value = “done”
Application.DisplayAlerts = False
Workbooks.Open ("C:\Users\Andrew\Dropbox (Updox)\All Company\DEPT-Finance\Billing\Greenway\Invoices\2015Invoices\Template.xlsx")
ActiveWorkbook.Sheets(“invoice”).Activate
ActiveWorkbook.Sheets("Invoice").Range("E7").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E5").Value = invoicenumber
ActiveWorkbook.Sheets("Invoice").Range("A16").Value = providercount
ActiveWorkbook.Sheets("Invoice").Range("A17").Value = faxlines
ActiveWorkbook.Sheets("Invoice").Range("A18").Value = faxpages
ActiveWorkbook.Sheets("Invoice").Range("A19").Value = faxbundles
ActiveWorkbook.Sheets("Invoice").Range("E8").Value = customerid
ActiveWorkbook.Sheets("Invoice").Range("D16").Value = basefee
ActiveWorkbook.Sheets(“invoice”).Range("E4").Value = invoicedate
path = "C:\Users\Andrew\Dropbox (Updox)\All Company\DEPT-Finance\Billing\Greenway\Invoices\2015Invoices\"
mydate = Date
mydate = Format(mydate, “mm.yy”)
ActiveWorkbook.SaveAs Filename:=path & “_” & customername & “_” & mydate & “.xlsx”
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut copies:=1
ActiveWorkbook.Close SaveChanges:=False
nextrow:
Next r
End Sub
也许您应该尝试此操作,以找到Col A中的最后一行,并解决“运行时错误1004”:
With Sheets("Greenway")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
或者:
lastrow = Sheets("Greenway").Range("A" & Sheets("Greenway").Rows.Count).End(xlUp).Row
代替:
lastrow = Sheets("Greenway").Range(“A” & Rows.Count).End(xlUp).Row
注意之前的点(.
)分隔符Rows
表示您要获取属性Rows OF Sheets(“ Greenway”)对象。
对于您现在得到的“运行时错误9”错误,请尝试:
customer = Sheets("Greenway").Cells(r, 3).Value
代替:
customer = ThisWorkbook.Sheets(“Greenway”).Cells(r, 3).Value
希望它有用!
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句