匹配列中的值（Application.Match）

Sub InvoiceWriter()

    ' Define constants.
 
    Const PROC_TITLE As String = "Invoice Writer"
    Const SRC_NAME As String = "Finance"
    Const SRC_FIRST_CELL As String = "A2"
    Const DST_NAME As String = "Invoice"

    Dim srcCols() As Variant, dstCells() As Variant
    ' Name, Email, Address, Date, Owed
    srcCols = Array("B", "C", "D", "E", "I")
    dstCells = Array("D3", "D4", "D5", "B8", "D8")

    ' Input.

    Dim UserInput As String: UserInput = InputBox("Type Associated Letter " _
        & "corresponding to Desired Invoice Population:", PROC_TITLE)

    If Len(UserInput) = 0 Then
        MsgBox "Dialog canceled.", vbExclamation, PROC_TITLE
        Exit Sub
    End If
    
    ' Reference the workbook.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the Source Input range.
    
    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_NAME)
    
    Dim srg As Range
    With sws.Range(SRC_FIRST_CELL)
        Dim slCell As Range: Set slCell = .Resize(sws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If slCell Is Nothing Then
            MsgBox "No data found.", vbCritical, PROC_TITLE
            Exit Sub
        End If
        Set srg = .Resize(slCell.Row - .Row + 1)
    End With
    
    ' Retrieve the Source User Input row index.
    
    Dim srIndex As Variant: srIndex = Application.Match(UserInput, srg, 0)
    
    If IsError(srIndex) Then
        MsgBox "Could not find '" & UserInput & "'.", vbCritical, PROC_TITLE
        Exit Sub
    End If
    
    srIndex = srIndex + srg.Row - 1 ' convert range row to worksheet row
    
    ' Write the values from the Source to the Destination cells.
    
    Dim dws As Worksheet: Set dws = wb.Sheets(DST_NAME)
    
    Dim n As Long
    For n = LBound(srcCols) To UBound(srcCols)
        dws.Range(dstCells(n)).Value = sws.Cells(srIndex, srcCols(n)).Value
    Next n
    
    ' Inform.
    
    MsgBox "Invoice populated.", vbInformation, PROC_TITLE

End Sub