|
本帖最后由 MR.chlive 于 2020-2-26 21:32 编辑
- Sub search()
- On Error Resume Next
- 'If Target.Address <> "$A$1" Then Exit Sub '如果活动单元格不是A1则退出程序
- 'Range(Rows("2:2"), Selection.End(xlDown)).ClearContents '将第一行以外的内容清除
- Dim sht As Worksheet, arr(), i As Integer
- Dim rng As Range, mrng As Range, target
- Dim findstr As String
- target = "无视频段"
- For Each sht In Sheets '遍历所有工作表
- If sht.Name <> "汇总" Then '排除查询表
- Set rng = sht.UsedRange.find(what:=target, LookIn:=xlValues, lookat:=xlPart) '开始查找
- If rng Is Nothing Then GoTo line '如果未找到则跳转至标签line
- 'Set mrng = rng '将找到的对象赋值给另一个变量
- findstr = rng.Address
- Do
- i = i + 1 '累加变量
- ReDim Preserve arr(1 To 5, 1 To i) '重置数组变量存储空间
- arr(1, i) = rng.Offset(rng.Row, 1 - rng.Column) '日期
- If rng.Offset(3 - rng.Row, -2) = "" Then '工位名称为空判定,调整列偏移
- arr(2, i) = rng.Offset(3 - rng.Row, -1)
- Else
- arr(2, i) = rng.Offset(3 - rng.Row, -2)
- End If
- arr(3, i) = rng.Offset(rng.Row, -1) '断点时间
- arr(4, i) = rng.Text '断点原因
- arr(5, i) = sht.Name
-
- Set rng = sht.UsedRange.FindNext(rng) '查找下一个
- Loop While findstr = rng.Address '直到查找到的单元格的地址等于第一个单元格地址时停止
-
- End If
- line:
- Next
- [a2].Resize(i, 5) = WorksheetFunction.Transpose(arr) '将数组倒置后写入列表
- End Sub
复制代码
核查表.7z
(311.27 KB, 下载次数: 2)
代码如上,始终只能返回第一个工作表的匹配内容,请教这是为什么呢?可以不用字典吗?请大神看附件样板数据。
|
|