我也來練習一下,試試看
Sub ex()
Dim arr As Variant, a As Variant, b As Variant
Dim d As Object, x%, y%, DSC%
Set d = CreateObject("scripting.dictionary")
DSC = InputBox("輸入折扣%", "折扣", "60")
With Sheets("工作表1")
With .Range(.[a1], [k1].End(4))
.Borders.LineStyle = xlLineStyleNone
.UnMerge
.ClearContents
End With
For Each a In .Range(.[O1], .[O1].End(4))
If Not d.exists(a.Value) Then d(a.Value) = ""
Next
End With
For x = 1 To Sheets.Count - 1
With Sheets(x)
For Each a In .UsedRange
If d.exists(a.Value) Then
For Each b In Array(Array("C", "S220"), Array("M", "M520"), Array("N", "N620"), Array("A", "S220焊接"), Array("H", "HSS"), Array("K", "HSS-Co"), Array("S", "SKH"))
If b(0) = Left(a.Value, 1) Then d(a.Value) = b(1): Exit For
Next
arr = Array(.[a1] & "--" & .Name & "第" & a.Row & "列", Chr(10))
For y = 1 To .[a2].End(2).Column
ReDim Preserve arr(0 To UBound(arr) + 1)
arr(UBound(arr)) = .Cells(2, y) & "*" & .Cells(a.Row, y) & " "
Next
d(a.Value) = Array(d(a.Value), Join(arr, ""), "", "", "", "", "", "=Roundup((" & a.Offset(, 1).Value & " * " & DSC / 100 & "), -1)", "", a.Value)
End If
Next
End With
Next
With Sheets("工作表1")
.[b1].Resize(d.Count, 10) = Application.Transpose(Application.Transpose(d.items))
With .Range([a1], [k1].End(4))
For x = 1 To .Rows.Count
.Cells(x, 1) = x
.Cells(x, 3).Resize(, 5).Merge
.Cells(x, 10) = "=Sum(I" & x & "*H" & x & ")"
Next
.Borders.LineStyle = xlContinuous
End With
End With
End Sub作者: agietron 時間: 2021-4-7 20:16
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作者: agietron 時間: 2021-4-8 20:16
