|
请参考:
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address(0, 0) <> "B7" Then Exit Sub
- If Target = "" Then Exit Sub
- Dim arr, brr(1 To 100000, 1 To 2), i&, m&, t$, sh As Worksheet
- t = Target
- For Each sh In Worksheets
- If sh.Name <> "首页" Then
- arr = sh.Range("B5:C" & sh.Range("B" & Rows.Count).End(xlUp).Row)
- For i = 1 To UBound(arr)
- If InStr(arr(i, 1), t) Then
- m = m + 1
- brr(m, 1) = arr(i, 1)
- brr(m, 2) = arr(i, 2)
- End If
- Next
- End If
- Next
- Range("A9:B" & Rows.Count).ClearContents
- If m > 0 Then Range("A9").Resize(m, 2) = brr
- End Sub
复制代码 |
|