|
本帖最后由 bbcc100 于 2020-3-15 00:39 编辑
本工作簿只有二张工作表,分别是《查询》《余额表》
当《查询》N2单元格输入,比如“城”字时,在《余额表》C列查到包含“城”的行次,把《余额表》B、C、H、I列相应的信息提取出来,放到《查询》表的O、P、Q、R列(这点已实现)
再在《查询》N3单元格输入,比如“林”字时,再把《查询》表P列包含“林”字的《查询》表的O、P、Q、R列对应的行次留下来(这一点,不知哪的代码有问题,不能实现?)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> [n2].Address Then Exit Sub
cxnr = Sheet11.[n2]
If cxnr = "" Then Exit Sub
r = Sheet3.[b1048576].End(3).Row - 1
arr = Sheet3.Range("B2:I" & r + 1)
For i = 1 To r
If InStr(1, arr(i, 2), cxnr) > 0 Then
n = n + 1
arr(n, 1) = arr(i, 1)
arr(n, 2) = arr(i, 2)
arr(n, 3) = arr(i, 7)
arr(n, 4) = arr(i, 8)
End If
Next
Sheet11.[o2:r1048576].Clear
If n > 0 Then Sheet11.[o2].Resize(n, 4) = arr
'==================================================
If Target.Address = [n3].Address Then 'Exit Sub
cxnr1 = Sheet11.[n3]
If cxnr1 = "" Then Exit Sub
r = Sheet11.[p1048576].End(3).Row - 1
arr = Sheet11.Range("o2:R" & r + 1)
For i = 1 To r
If InStr(1, arr(i, 2), cxnr1) > 0 Then
n = n + 1
For j = 1 To 4
arr(n, j) = arr(i, j)
Next
End If
Next
Sheet11.[o2:r1048576].Clear
If n > 0 Then Sheet11.[o2].Resize(n, 4) = arr
End If
End Sub
|
|