Sub invoiceConcat() ''工作表名稱 Const kWSName As String = "工作表2" ''使用範圍列數 Dim kRow As Integer ''外層For迴圈計數器 Dim kCNT As Integer ''內層For迴圈計數器 Dim kCounter As Integer ''用來當作索引的發票號碼 Dim kTarget As String ''發票明細 Dim kDetail As String ThisWorkbook.Worksheets(kWSName).Select With ActiveSheet ''取得最後一列列號 kRow = .Range("A65536").End(xlUp).Row For kCNT = 3 To kRow ''發票號碼 kTarget = .Cells(kCNT, 7) For kCounter = 1 To kRow If .Cells(kCounter, 2) = kTarget Then ''可自己設定所需的明細格式 kDetail = kDetail & Trim(.Cells(kCounter, 4)) & " [NT$ " & .Cells(kCounter, 3) & "]" & Chr(13) End If Next kCounter ''刪除句尾多的一個換行符號 If kDetail <> "" Then .Cells(kCNT, 10) = Mid(kDetail, 1, Len(kDetail) - 1) End If ''明細字串重置 kDetail = "" Next kCNT End With End Sub
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter