|
发表于 2017-6-3 15:29
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim wt As Worksheet
- Dim i, i1, i2, r, AA As Integer
- Dim Arr
- Dim regex
- Set regex = CreateObject("vbscript.regexp")
- regex.Pattern = "\d+"
- AA = regex.Execute(Sheets("目录").Cells(1, 1).Value)(0) * 1
- Sheets("目录").Range("A3").Resize(10000, 15).Clear
- For Each wt In Worksheets
- If wt.Name <> "目录" Then
- r = wt.Range("A65536").End(xlUp).Row
- wt.Cells(r, 5) = WorksheetFunction.Sum(wt.Range(Cells(4, 5).Address, Cells(r - 1, 5).Address))
- wt.Cells(r, 6) = WorksheetFunction.Sum(wt.Range(Cells(4, 6).Address, Cells(r - 1, 6).Address))
- For i1 = 4 To r - 1
- wt.Cells(i1, 7) = wt.Cells(i1 - 1, 7) + wt.Cells(i1, 6) - wt.Cells(i1, 5)
- wt.Cells(r, 7) = wt.Cells(r - 1, 7)
- Next
- BB = regex.Execute(wt.Cells(r, 1).Value)(0) * 1
- i2 = Sheets("目录").Range("B65536").End(xlUp).Row + 1
- If AA = BB Then
- Sheets("目录").Cells(i2, 2) = wt.Name
- Sheets("目录").Cells(i2, 3) = wt.Cells(r, 7)
- Sheets("目录").Cells(i2, 7) = wt.Cells(r, 5)
- Sheets("目录").Cells(i2, 11) = wt.Cells(r, 6)
- Sheets("目录").Cells(i2, 15) = wt.Cells(3, 7)
- ActiveSheet.Hyperlinks.Add Anchor:=Cells(i2, 2), Address:="", _
- SubAddress:=wt.Name & "!A1", TextToDisplay:=Cells(i2, 2).Value
- Else
- Sheets("目录").Cells(i2, 2) = wt.Name
- Sheets("目录").Cells(i2, 3) = wt.Cells(r, 7)
- Sheets("目录").Cells(i2, 15) = wt.Cells(3, 7)
- ActiveSheet.Hyperlinks.Add Anchor:=Cells(i2, 2), Address:="", _
- SubAddress:=wt.Name & "!A1", TextToDisplay:=Cells(i2, 2).Value
- End If
- End If
- Next
- i2 = Sheets("目录").Range("B65536").End(xlUp).Row
- For i3 = 3 To i2
- Sheets("目录").Cells(i3, 1) = i3 - 2
- Sheets("目录").Cells(i3, 5) = i3 - 2
- Sheets("目录").Cells(i3, 9) = i3 - 2
- Sheets("目录").Cells(i3, 13) = i3 - 2
- Next
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|