- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2024-10-30
|
15#
發表於 2021-10-9 05:47
| 只看該作者
回復 14# oak0723-1
我增加資料列數和欄數
發現不能隨增減資料數變動比對結果
>> 已更新，請再測試看看，謝謝
Sub test()
Dim Arr, Drr, Brr(), Crr(), T, XMax, XMin, R%, C%
Tm = Timer
With Sheets("資料庫")
R = .[b6].End(4).Row
C = .Cells(5, Columns.Count).End(1).Column
Arr = .Range(.[b6], .Cells(R, 2))
Drr = .Range(.[i6], .Cells(R, C))
End With
With Sheets("比對")
.[b6].Resize(UBound(Arr)) = Arr
ReDim Brr(1 To UBound(Arr), 1 To UBound(Drr, 2))
ReDim Crr(1 To UBound(Arr), 1 To 1)
C = .Cells(3, Columns.Count).End(1).Column
Arr = .Range(.[i3], .Cells(4, C))
For i = 1 To UBound(Drr)
Crr(i, 1) = 0
For j = 1 To UBound(Arr, 2)
T = Drr(i, j): XMin = Arr(1, j): XMax = Arr(2, j)
If XMin = "" Or T = "" Then Brr(i, j) = "": GoTo 99
If T < XMin Or T > XMax Then
Brr(i, j) = 0
Else
Brr(i, j) = 1: Crr(i, 1) = Crr(i, 1) + 1
End If
99: Next j
Next i
.Range("h6").Resize(UBound(Crr)) = Crr
.Range("i6").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
End With
MsgBox Timer - Tm
End Sub |
|
