|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub tt33()
Dim A()
Sheet1.[A2:C65000].ClearContents
For i = 1 To Sheets.Count
N = Sheets(i).Name
If Sheets(i).Name <> "优秀员工" Then
Set rng = Sheets(i).Cells.Find("优秀")
If Not rng Is Nothing Then
FD = rng.Address
m = m + 1
ReDim Preserve A(1 To 3, 1 To m)
A(1, m) = Sheets(i).Name
A(2, m) = rng.End(1)
A(3, m) = "优秀"
'-----------------------
Do
Set rng = Sheets(i).Cells.FindNext(rng)
LD = rng.Address
If FD <> LD Then
m = m + 1
ReDim Preserve A(1 To 3, 1 To m)
A(1, m) = Sheets(i).Name
A(2, m) = rng.End(1)
A(3, m) = "优秀"
End If
Loop Until FD = LD
End If
End If
Next
'---------------------------------
A = Application.Transpose(A)
Sheet1.[A2].Resize(UBound(A), UBound(A, 2)) = A
End Sub |
|