Sub TEST_A1()
Dim Arr, Brr(2), N&(2), Crr, T$, V(2), i&, j%, k%
Sheet1.UsedRange.Offset(1, 1).Clear
V(1) = Val([a3]): V(2) = Val([a5])
Arr = Range(Sheet2.[A1], Sheet2.UsedRange)
For i = 3 To UBound(Arr)
T = Right(Arr(i, 2), 2)
k = Switch(T = "MV", 1, T = "PV", 2, T = T, 0)
If k = 0 Then GoTo 101
If Val(Arr(i, 7)) <= V(k) Then GoTo 101
Crr = Brr(k)
If Not IsArray(Crr) Then ReDim Crr(1 To UBound(Arr), 1 To 4)
N(k) = N(k) + 1
For j = 1 To 4
Crr(N(k), j) = Arr(i, Mid(1267, j, 1))
Next j
Brr(k) = Crr
101: Next i
For k = 1 To 2
If N(k) = 0 Then GoTo 102
With Sheet1.[B65536].End(xlUp)(2).Resize(N(k), 4)
.Value = Brr(k)
.Columns(3).NumberFormatLocal = "0.0"
.Columns(4).NumberFormatLocal = IIf(k = 2, "0.0%", "0.0")
.Sort Key1:=.Item(4), Order1:=xlDescending, Header:=xlNo
End With
102: Next k
End Sub
Xl0000265.rar
(17.89 KB, 下载次数: 8)
|