Sub 查詢()
Dim Arr, Brr, T$, Sht As Worksheet, i&, j%, Mr, N&
T = [D3]: If T = "" Then MsgBox "未輸入查詢字串!! ": Exit Sub
[B7:M3000].ClearContents
Mr = Array(1, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
ReDim Brr(1 To 30000, 1 To 12)
For Each Sht In Sheets
If Sht.Name = "整合查詢" Then GoTo 101
Arr = Sht.UsedRange
For i = 2 To UBound(Arr)
If InStr(Arr(i, 4), T) Then
N = N + 1: Brr(N, 1) = Sht.Name
For j = 1 To 11: Brr(N, j + 1) = Arr(i, Mr(j - 1)): Next
End If
Next i
101: Next
If N > 0 Then [B7:M7].Resize(N) = Brr
End Sub作者: jesscc 時間: 2019-2-26 20:54