既然已经遍历判断出入库了,那么干脆一次遍历解决所有问题,不再用sql。
- Sub chaxun()
- Dim hm, cel As Range
- Sheets(1).Activate
- If Range("j3").Value = "" Then
- MsgBox "请输入需要查询的单据号码"
- Exit Sub
- End If
- Range("c6:j11").Value = ""
- Range("d3,f3,d14,f14,j14").Value = ""
- hm = CStr(Range("j3").Value)
- arr = Sheets("数据库").[a1].CurrentRegion
- Dim brr(1 To 6, 1 To 8)
- For i = 2 To UBound(arr)
- If CStr(arr(i, 4)) = hm Then
- n = n + 1
- brr(n, 1) = arr(i, 5)
- brr(n, 2) = arr(i, 6)
- brr(n, 3) = arr(i, 7)
- brr(n, 4) = arr(i, 8)
- brr(n, 5) = arr(i, 9) + arr(i, 12)
- brr(n, 6) = arr(i, 10) + arr(i, 13)
- brr(n, 7) = arr(i, 11) + arr(i, 14)
- brr(n, 8) = arr(i, 15)
- crk = arr(i, 1)
- bm = arr(i, 2)
- rq = arr(i, 3)
- kg = arr(i, 16)
- sh = arr(i, 19)
- ry = arr(i, 17) & arr(i, 18)
- End If
-
- Next
- If n > 6 Then MsgBox "超出显示范围": Exit Sub
- If n > 0 Then
- Range("b2") = crk '出入库
- Range("d3") = bm '部门cel.Offset(0, -2).Value
- Range("f3") = rq '日期cel.Offset(0, -1).Value
- Range("d14") = kg '库管cel.Offset(0, 12).Value
- Range("j14") = sh '审核 cel.Offset(0, 15).Value
- Range("e14") = IIf(crk = "入库单", "采购员", "领用人")
- Range("f14") = ry '采购员 & 领用人 cel.Offset(0, 14).Value
- [c6].Resize(n, 8) = brr
- End If
- End Sub
复制代码 |