- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2023-9-21
|
回復 9# agietron
請測試看看，謝謝。
Sub TEST()
Dim Arr, Brr, xD, Frr(1 To 10000, 1 To 11), T, T1, TT, i&, j&, DSC%, sht%, y%, K%
Set xD = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
DSC = InputBox("輸入折扣%", "折扣", "60")
TM = Timer
Arr = Range([工作表1!O1], [工作表1!O65535].End(3))
For i = 1 To UBound(Arr)
xD(Arr(i, 1) & "") = i
If Right(Arr(i, 1), 1) <> "A" Then
xD(Arr(i, 1) & "A" & "") = i
End If
Next
For sht = 1 To Sheets.Count - 1
Brr = Sheets(sht).UsedRange
For i = 2 To UBound(Brr)
For j = 1 To UBound(Brr, 2)
If xD.Exists(Brr(i, j) & "") Then
K = K + 1: TT = "": T = Left(Brr(i, j), 1)
If T = "C" Then
T1 = "S220"
ElseIf T = "M" Then
T1 = "M520"
ElseIf T = "N" Then
T1 = "N620"
ElseIf T = "A" Then
T1 = "S220焊接"
ElseIf T = "H" Then
T1 = "HSS"
ElseIf T = "K" Then
T1 = "HSS-Co"
ElseIf T = "S" Then
T1 = "SKH"
End If
For y = 1 To 6: TT = TT & Brr(2, y) & Brr(i, y) & " * ": Next
Frr(K, 1) = K
Frr(K, 2) = T1
Frr(K, 3) = Brr(1, 1) & "--" & Sheets(sht).Name & " 第" & i - 2 & "列" & Chr(10) & Mid(TT, 1, Len(TT) - 3)
Frr(K, 9) = "=RoundUp((" & (Brr(i, j + 1) & "*" & DSC / 100) & "), -1)"
Frr(K, 10) = "=sum(i" & K & "*h" & K & ")"
Frr(K, 11) = Brr(i, j)
xD.Remove (Brr(i, j))
End If
Next
Next
Next
With Sheets("工作表1")
With Range(.[a1], .[k1].End(4))
.Value = ""
.UnMerge
.Borders.LineStyle = 0
End With
With .[a1].Resize(K, 11)
.Value = Frr
.Borders.LineStyle = 1
For i = 1 To K: .Cells(i, 3).Resize(, 5).Merge: Next
End With
.Range("o1:o" & UBound(Arr)).Font.Color = RGB(0, 0, 0)
For i = 1 To UBound(Arr)
If xD.Exists(Arr(i, 1) & "") Then .Cells(i, 15).Font.Color = RGB(255, 0, 0)
Next
End With
Application.ScreenUpdating = True
MsgBox "已完成! 總共：" & Timer - TM & "秒 !"
End Sub |
|
發表於 2021-4-8 11:25
| 