|
请教各位老师,因为馆藏图书中的书名是固定的,但是会有一些其他的字段,比如时间,比如柜号等等,但是平时不需要这些字段,所以在绘本管理工作表就没有写全,
现在主要通过条形码来查询,仍然有一些问题,不能查询全。希望各位老师能够帮忙想想办法。感谢。
这个是历史借阅工作表
这是馆藏图书工作表
这是绘本管理工作表
这是三个表的逻辑关系
现目前实现部分功能代码
- Sub 图书排名2()
- Dim arr, brr, i&, r&, nr&, X, d, d1, d2, j, wb As Workbook, crr()
- Set d = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- 'Set d5 = CreateObject("Scripting.Dictionary")
- With Sheets("历史借阅")
- nr = .Range("f" & Rows.Count).End(3).Row
- brr = .Range("d2:d" & nr)
- arr1 = .Range("f2:i" & nr)
- ReDim arr(1 To UBound(arr1), 1 To 5)
- For i = 1 To UBound(arr1)
- arr(i, 1) = arr1(i, 1)
- arr(i, 2) = brr(i, 1)
- arr(i, 3) = arr1(i, 2)
- arr(i, 4) = arr1(i, 3)
- arr(i, 5) = arr1(i, 4)
- Next
- 'arr = .Range("f2:i" & nr)
- For i = 1 To UBound(arr)
- X = arr(i, 3)
- 'm = m + 1
- If Not d.exists(X) Then
- d(X) = arr(i, 1) & "-" & arr(i, 2)
- d1(X) = 1 'arr(i, 5)
- d2(X) = arr(i, 5)
- Else
- d(X) = d(X) & "/" & arr(i, 1) & "-" & arr(i, 2)
- d1(X) = d1(X) + 1 'arr(i, 5)
- End If
- Next
- 'j = [a65536].End(xlUp).Row
- 'Range("h2:s" & j).HorizontalAlignment = xlCenter
- 'Range("a" & j).CurrentRegion.Borders.LineStyle = xlContinuous
- '.[A1].Resize(1, 2) = Array("Number", "Name")
- .[j2].Resize(d.Count, 1) = Application.Transpose(d2.items)
- .[k2].Resize(d.Count, 1) = Application.Transpose(d.keys)
- .[l2].Resize(d.Count, 1) = Application.Transpose(d.items)
- .[m2].Resize(d.Count, 1) = Application.Transpose(d1.items)
- d.RemoveAll
- d1.RemoveAll
- arr = .Range("j2:m" & nr)
-
- End With
- With Sheets("绘本管理")
- j = .Range("b" & Rows.Count).End(3).Row
- brr = .Range("b2:d" & j)
- For i = 1 To UBound(arr)
- X = Val(arr(i, 1))
- If Not d.exists(X) Then
- d(X) = Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4))
- End If
- Next
- For r = 1 To UBound(brr)
- x2 = Val(brr(r, 3))
- If d.exists(x2) Then
- brr(r, 2) = d(x2)(2)
- brr(r, 3) = d(x2)(3)
- Else
- brr(r, 2) = ""
- brr(r, 3) = ""
- End If
- Next
- .[j2].Resize(UBound(brr), UBound(brr, 2)) = brr
-
- 'Sheets("历史借阅").[n2].Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
-
- End Sub
复制代码
书名两两模糊匹配.rar
(397.68 KB, 下载次数: 18)
|
|