|
发表于 2016-11-23 11:34
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Change(ByVal T As Range)
- Dim arr(0 To 60000, 1 To 3), stmArr, n As Integer
- Dim strPath As String, i As Long, strName As String
- Dim wb As Workbook, wbName As String, sh As Worksheet
- If T.Address <> "$A$2" Then Exit Sub '当不是A2单元格时,退出
- Application.ScreenUpdating = False
- strPath = ThisWorkbook.Path & ""
- strName = Dir(strPath & "*.XLS*")
- arr(0, 1) = "姓名": arr(0, 2) = "性别": arr(0, 3) = "工作簿名称"
- Do While strName <> ""
- wbName = Split(strName, ".")(0)
- If wbName <> "性别" Then
- Set wb = Workbooks.Open(strPath & strName)
- For Each sh In wb.Sheets
- If sh.Range("a1") = "" Then GoTo 10
- stmArr = sh.UsedRange
- For n = 1 To UBound(stmArr)
- If stmArr(n, 2) = T.Value Then
- i = i + 1
- arr(i, 1) = stmArr(n, 1)
- arr(i, 2) = stmArr(n, 2)
- arr(i, 3) = wbName
- End If
- 10
- Next
- Next
- wb.Close: Set wb = Nothing
- End If
- strName = Dir
- Loop
- Range("b2:d60002") = arr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|