'可以是全部条件,你这7个条件当然也是可以用的
Option Explicit
Sub zz()
Dim j, i, arr, brr, n, first, last, cond
With Sheets("数据来源")
arr = .Range("a2:r" & .Cells(Rows.Count, "a").End(xlUp).Row)
End With
ReDim brr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
With Sheets("查询")
first = .[c1]: last = .[c2]: cond = .[a4:r4]
For i = 1 To UBound(arr, 1)
If arr(i, 1) <= last And arr(i, 1) >= first Then
For j = 1 To UBound(cond, 2)
If Len(cond(1, j)) > 0 Then
If InStr(arr(i, j), cond(1, j)) = 0 Then Exit For
End If
Next
If j = UBound(cond, 2) + 1 Then
n = n + 1
For j = 1 To UBound(cond, 2): brr(n, j) = arr(i, j): Next
End If
End If
Next
.[a6].Resize(.Cells(Rows.Count, "a").End(xlUp).Row, UBound(brr, 2)).ClearContents
If n > 0 Then .[a6].Resize(n, UBound(brr, 2)) = brr
End With
End Sub |