|
这几天学习了一下后改了一下代码使它可以跨工作蒲查询,
还在1号工作蒲里面加入了一个窗体,窗体查询的话不限制查询条件在哪行那列,可以修改返回结果
Dim x, y, i, t, a, b, c, d, c1 As Worksheet, c2 As Workbook
Set c1 = ActiveSheet '当前工作表
Set c2 = ThisWorkbook '代码所在工作蒲
x = c1.Cells(65535, 1).End(xlUp).Row '第一个工作表a有几行
i = c2.Sheets(1).Cells(65535, 1).End(xlUp).Row '第二个工作表a有几行
For a = 2 To x
For b = 1 To i
If c1.Cells(a, 1) = c2.Sheets(1).Cells(b, 1) Then '判断行重复
y = c1.Cells(1, 250).End(xlToLeft).Column '第一个工作表a1有几列
t = c2.Sheets(1).Cells(b, 250).End(xlToLeft).Column
For c = 2 To y
if cells(1,c)="" then
Exit For
End If
For d = 1 To t
If c1.Cells(1, c) = c2.Sheets(1).Cells(b, d) Then '判断列重复
c1.Cells(a, c) = 1 '有=1
Exit For '结束运行for d
Else
c1.Cells(a, c) = 2 '无=2
End If
Next
Next
End If
Next
Next
End Sub
|
|