- 帖子
- 354
- 主題
- 5
- 精華
- 0
- 積分
- 387
- 點名
- 0
- 作業系統
- windows7
- 軟體版本
- vba,vb,excel2007
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2017-1-8
- 最後登錄
- 2024-8-2
 
|
本帖最後由 singo1232001 於 2022-6-3 18:24 編輯
回復 1# oak0723-1
Sub 比對db()
T = Timer
Set s = Sheets("比對")
rw = s.Cells(Rows.Count, "i").End(3).Row
If rw <= 5 Then rw = 5
s.Range("h5:in" & rw).ClearContents
ar = s.[I3:IN5]
br = Sheets("資料庫").Range("H5:IN" & Sheets("資料庫").Cells(Rows.Count, "i").End(3).Row)
For i = 1 To UBound(br): br(i, 1) = 0: Next
For i = 1 To UBound(br, 2): br(1, i) = 0: Next
For i = 1 To UBound(ar, 2)
k = i + 1
If ar(1, i) <> "" Or ar(2, i) <> "" Then
For j = 2 To UBound(br)
If ar(1, i) <= br(j, k) Then
If br(j, k) <= ar(2, i) Then
br(1, k) = br(1, k) + 1
br(j, 1) = br(j, 1) + 1
br(j, k) = 1
Else
br(j, k) = 0
End If
Else
br(j, k) = 0
End If
Next
Else
For j = 2 To UBound(br): br(j, k) = "": Next
End If
Next
br(1, 1) = "合計"
s.[h5].Resize(UBound(br), UBound(ar, 2) + 1) = br
MsgBox Format(Timer - T, "0.0") & "秒"
End Sub
優化過 怕你量過大 |
|
發表於 2022-6-3 18:22
| 