excel - 将发票总额复制到相关客户帐户余额

Question

我正在为我的小型企业创建一个新的发票系统。

我需要一个按钮，将当前发票总额从主发票工作簿复制到相应客户名称和期限名称下的帐户余额工作簿。

在主发票工作簿中，我有客户名称(单元格 G10)、与它相关的学期(单元格 H2)和总发票金额。

在 Enrollment Database 工作簿中，我有一张名为 Account Balances 的工作表。 A 列包含每个客户名称，第 1 行包含学校学期名称(第 1 学期发票、第 2 学期发票等)。

当按下“添加到帐户余额”按钮时，我希望 Excel 在主发票中找到当前的客户名称和期限 (x) 发票，并将发票总额复制到注册数据库工作簿中的相应单元格。

示例:TEST TESTINGTON 的第 1 学期发票为 90 美元。当我按下按钮时，它会复制 90 美元并将其粘贴到单元格 B3 中，因为那是 TEST TESTINGTON(客户名称)和 Term 1 Invoice(术语名称)的交集

主发票备注



注册数据库说明



这是最新的代码，但我从来没有远程工作过

Private Sub AddToAccounts_Click()  
    Workbooks("Master Invoice.xlsm").Worksheets("Master Invoice").Range("j40").Copy
    Workbooks("Enrolment Database.xlsm").Worksheets("Account Balances").Range(Cells("A2:A150", "B1:E1").Address).PasteSpecial Paste:=xlPasteValues  
End Sub

Answer 1

下面是如何将发票数据复制到 Assets 负债表的示例。此示例中的重要内容是:

Always use Option Explicit

声明和使用中间变量。这极大地帮助您将工作重点放在代码逻辑的那部分您所关注的工作簿、工作表和信息上。例如，分别定义当前工作簿和注册工作簿。这样，您就不必不断地处理很长的引用层次结构。

所以不要这样做:

Workbooks("Master Invoice.xlsm").Worksheets("Master Invoice").Range("j40").Copy
Workbooks("Enrolment Database.xlsm").Worksheets("Account Balances").Range(Cells("A2:A150", "B1:E1").Address).PasteSpecial Paste:=xlPasteValues  

做这个:

Dim invoiceWS As Worksheet
Set invoiceWS = ThisWorkbook.Sheets("Master Invoice")

Dim enrollmentWB As Workbook
Dim balancesWS As Worksheet
Set enrollmentWB = Workbooks.Open("Enrollment Database.xlsm")
Set balancesWS = enrollmentWB.Sheets("Account Balances")

这是作为单个子的整个示例。请注意错误检查以防止您的代码崩溃以及变量名称如何自我记录代码逻辑。

Option Explicit

Sub AddToAccounts_Click()
    Dim invoiceWS As Worksheet
    Set invoiceWS = ThisWorkbook.Sheets("Master Invoice")

    '--- collect the data on the current invoice
    Dim customer As String
    Dim currentInvoice As Double
    Dim currentTerm As String
    With invoiceWS
        customer = .Range("G10").Value
        currentTerm = .Range("H1").Value
        currentInvoice = .Range("J40").Value
    End With

    Dim enrollmentWB As Workbook
    Dim balancesWS As Worksheet
    Dim enrollmentWBWasOpen As Boolean
    Set enrollmentWB = ThisWorkbook
    'Set enrollmentWB = GetWorkbook("C:\Temp\Enrollment Database.xlsm", _
                                    enrollmentWBWasOpen)
    Set balancesWS = enrollmentWB.Sheets("Account Balances")

    '--- find the row with that holds the current customer's invoices
    Dim customerBalances As Range
    Set customerBalances = balancesWS.Range("A:A").Find(What:=customer)
    If customerBalances Is Nothing Then
        MsgBox "ERROR: Customer not found! (" & customer & ")"
        '--- optional??
        If Not enrollmentWBWasOpen Then
            enrollmentWB.Close
        End If
        Exit Sub
    End If

    '--- now find the column that matches the term
    Dim term As Range
    Set term = balancesWS.Range("1:1").Find(What:=currentTerm)
    If term Is Nothing Then
        MsgBox "ERROR: Current term not found! (" & currentTerm & ")"
        '--- optional??
        If Not enrollmentWBWasOpen Then
            enrollmentWB.Close
        End If
        Exit Sub
    End If

    '--- copy the invoice value
    balancesWS.Cells(customerBalances.Row, term.Column).Value = currentInvoice

    If Not enrollmentWBWasOpen Then
        enrollmentWB.Close
    End If

End Sub

Public Function GetWorkbook(ByVal sFullName As String, _
                            Optional ByRef wasAlreadyOpen As Boolean) As Workbook
    '--- credit to: https://stackoverflow.com/a/9382034/4717755
    Dim sFile As String
    Dim wbReturn As Workbook

    sFile = Dir(sFullName)

    On Error Resume Next
        Set wbReturn = Workbooks(sFile)

        If wbReturn Is Nothing Then
            Set wbReturn = Workbooks.Open(sFullName)
            wasAlreadyOpen = False
        Else
            wasAlreadyOpen = True
        End If
    On Error GoTo 0

    Set GetWorkbook = wbReturn

End Function