|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
只能以摸擬結果當參考寫,以初入VBA者而言不算容易,
繁簡文字在VBE無法相容,僅能如下參考,也無法再進一步說明:
- Sub TEST()
- Dim xD, A As Range, C%, ClmnCunt%, TL1%, TL2%
- Set xD = CreateObject("Scripting.Dictionary")
- With Sheets("VBA").UsedRange
- .Offset(2, 0).EntireRow.Delete '清除原資料
- For Each A In .Rows(2).Cells '第2行標題(事先須手動輸入)
- C = A.Column
- If A <> "" Then xD(A.Value) = C '記錄〔產品〕所在〔列號〕
- If A = "Total" Then If TL1 = 0 Then TL1 = C Else TL2 = C '記錄〔合計〕的〔列號〕
- Next
- ClmnCunt = .Columns.Count '〔列〕總數
- End With
- Dim Arr, Brr, i&, j%, D(2), T$, R&, N&, U%
- Arr = Sheets("Sheet1").UsedRange
- ReDim Brr(1 To UBound(Arr), 1 To ClmnCunt)
- For i = 4 To UBound(Brr)
- D(0) = Arr(i, [AH1].Column) '開具日期
- D(1) = Arr(i, [BB1].Column) '入庫日期
- If D(0) = "" And D(1) = "" Then GoTo 101
- D(2) = IIf(D(0) = "", D(1), D(0))
- C = xD(Arr(i, 3)): If C = 0 Then GoTo 101 '取得〔產品〕列號
- U = IIf(C > TL2, TL2, TL1) '取得〔合計〕列號
- T = Arr(i, [BN1].Column) & "(" & Arr(i, [AP1].Column) & ")" '〔鄉鎮.公司〕名稱
- R = xD(T)
- If R = 0 Then xD(T) = N + 1: R = N + 1: N = N + 3
- For j = 0 To 2
- Brr(R + j, 1) = Format(Split(D(2) & " ", " ")(0), "yyyy/m/1")
- Brr(R + j, 2) = Array("A", "B", "C")(j) 'A〔征收〕,B〔入庫〕.C〔在途〕
- Brr(R + j, 3) = T
- If j < 2 And D(j) <> "" Then
- Brr(R + j, C) = Brr(R + j, C) + Arr(i, [I1].Column) '產品數量
- Brr(R + j, U) = Brr(R + j, U) + Arr(i, [I1].Column) '合計數量
- Brr(R + j, 4) = Brr(R + j, 4) + Arr(i, [I1].Column) '總計數量
- End If
- Next
- Brr(R + 2, C) = Brr(R, C) - Brr(R + 1, C) '在途產品數量
- Brr(R + 2, U) = Brr(R, U) - Brr(R + 1, U) '在途合計數量
- Brr(R + 2, 4) = Brr(R, 4) - Brr(R + 1, 4) '在途總計數量
- 101: Next i
- If N = 0 Then Exit Sub
- With Sheets("VBA").[A3].Resize(N, ClmnCunt)
- .Value = Brr
- .Replace 0, "", Lookat:=xlWhole
- .Borders.LineStyle = 1
- End With
- End Sub
复制代码
20160517VBA_v01.rar
(17.91 KB, 下载次数: 26)
|
评分
-
1
查看全部评分
-
|