Sub Macro2()
Application.ScreenUpdating = False '禁用刷新
Range("B4:D270").ClearContents
Sheets("Sheet1").Select
a = ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count).Row '行
b = ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count).Column '列
m = 1
f = Application.CountIf(Range(Cells(1, 1), Cells(a, b)), Sheets("sheet2").[B3].Value)
For r = 1 To f
Cells.Find(What:=Sheets("sheet2").[B3].Value, After:=ActiveCell).Activate
x = ActiveCell.Row
y = ActiveCell.Column
If Cells(x, y + 1) = Sheets("sheet2").[C3] And Cells(x, y + 2) = Sheets("sheet2").[D3] Then
z = Replace(Mid(Cells(x, y).Address, 2, 2), "$", "") ' 由列数得到列标
s = Replace(Mid(Cells(x, y + 1).Address, 2, 2), "$", "")
t = Replace(Mid(Cells(x, y + 2).Address, 2, 2), "$", "")
Sheets("sheet2").Cells(m + 3, 2).Resize(1, 3) = Array(z, s, t)
m = m + 1
End If
Next
'----提示
If m = 1 Then
MsgBox "对不起,没找到", , "山风提示"
Else
MsgBox "共找到" & m - 1 & "组数据", , "山风提示"
End If
Sheets("Sheet2").Select
End Sub
副本Xl0000002.rar
(11.91 KB, 下载次数: 30)
|