- ©«¤l
- 976
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 1018
- ÂI¦W
- 0
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-4-19
- ³Ì«áµn¿ý
- 2024-10-30
|
¦^´_ 9# agietron
½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
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("¿é¤J§é¦©%", "§é¦©", "60")
TM = Timer
Arr = Range([¤u§@ªí1!O1], [¤u§@ªí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²k±µ"
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 & "¦C" & 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("¤u§@ªí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 "¤w§¹¦¨! Á`¦@¡G" & Timer - TM & "¬í !"
End Sub |
|