我有一个超过20个选项卡的大型电子表格。每个TAB都有相同的结构
A=ref,B= Discipline, C=Location, D=item/location, E=Defect, F=Date, G=%complete
我想做的是在整个工作簿中搜索G列,并返回未列出"100"的所有项目的列表。结果将显示在摘要页面上。
随着时间的推移,项目列表将变得更小,因为更多的100项被输入到G列。
你能告诉我怎么做吗?由于
您可以使用Range。在VBA中查找方法
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx假设A栏中一直有内容,并将A:G栏复制到一个名为Summary的表单
Sub Find100Percent()
Dim wrkSht As Worksheet
Dim rFoundCell As Range
Dim sFirstAddress As String
Dim shtSummary As Worksheet
Set shtSummary = ThisWorkbook.Worksheets("Summary")
For Each wrkSht In ThisWorkbook.Worksheets
If wrkSht.Name <> shtSummary.Name Then
With wrkSht.Columns(7)
Set rFoundCell = .Find(1, LookIn:=xlValues)
If Not rFoundCell Is Nothing Then
sFirstAddress = rFoundCell.Address
Do
rFoundCell.Offset(, -6).Resize(, 7).Copy _
shtSummary.Cells(shtSummary.Rows.Count, 1).End(xlUp).Offset(1)
Set rFoundCell = .FindNext(rFoundCell)
Loop While Not rFoundCell Is Nothing And rFoundCell.Address <> sFirstAddress
End If
End With
End If
Next wrkSht
End Sub