|
略微改下,先清除工作表原有数据。- Sub test()
- Dim arr, arrResult
- Dim i As Integer, j As Long
- Dim objSht As Worksheet
- Dim lRecord As Long
- Dim lMatch
- '引用写入工作表
- Set objSht = Worksheets("sheet0")
- '清除原有数据
- With objSht
- .UsedRange.ClearContents
- End With
- lMatch = Application.InputBox("请输入要匹配的数值", "匹配查找", 92, , , , , 1)
- If lMatch = False Then
- MsgBox "没有输入要匹配的数值" & "退出", vbCritical + vbOKOnly
- Exit Sub
- End If
- '关属性
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .EnableEvents = False
- .Calculation = xlCalculationManual
- End With
- '工作表循环
- For i = 1 To Worksheets.Count - 1
- On Error Resume Next
- '防止访问到不存在的工作表
- If Len(Worksheets(CStr(i)).Name) = 0 Then
- GoTo next1
- End If
- On Error GoTo ErrorHandler
- '取源数据的D-F列
- With Worksheets(CStr(i))
- arr = .UsedRange.Columns("d:f").Value
- End With
- '重定义数组,和源数据数组行数相同
- ReDim arrResult(1 To UBound(arr), 1 To 1)
- '当前数组内写入数据个数
- lRecord = 0
- For j = LBound(arr) To UBound(arr)
- '进行匹配,符合条件的写入数组内
- 'Debug.Assert arr(j, 3) <> lMatch
- If arr(j, 3) = lMatch Then
- lRecord = lRecord + 1
- arrResult(lRecord, 1) = arr(j, 1)
- End If
- Next
- With objSht
- '有符合要求的数据时,进行写入操作
- If lRecord Then
- .Cells(1, i).Resize(UBound(arrResult)).Value = arrResult
- End If
- End With
- next1:
- Next
- '释放对象变量
- Set objSht = Nothing
- '打开属性
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- '对话框提示完成
- MsgBox "提取完成"
- Exit Sub
- ErrorHandler:
- '错误处理,主要是针对数据无法写入工作表的情况
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- Err.Clear
- '开属性
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- End Sub
复制代码 |
|