不要自动筛选和高级筛选的语句
只用循环语句完成
要求A表的7/9/6列的老师姓名、老师级别、老师课程三列的数据不重复的复制到B表的1/2/3别
我用的思路先复制第一行到B表初始数据
再循环A表和B表进行比较
重复的下一步
不重复的写到B表
但是代码就是不爽，谢谢~~~~~~~~~~~~~~~
- VB code
'测试《课程信息》有多少行数据hangsht2 = 3Do Until sht3.Cells(hangsht2, 6) = ""hangsht2 = hangsht2 + 1Loopk = 3kk = 3i = 4'《课程信息》每行和《乐团训练记录》比较，不同就增加While i < hangsht2For k = kk To i - 1If sht3.Cells(i, 5) = sht5.Cells(k, 1) And sht3.Cells(i, 6) = sht5.Cells(k, 2) And sht3.Cells(i, 7) = sht5.Cells(k, 3) And sht3.Cells(i, 8) = sht5.Cells(k, 4) And sht3.Cells(i, 10) <> sht5.Cells(k, 8) Theni = i + 1ElseFor j = 1 To 4sht5.Cells(kk + 1, j) = sht3.Cells(i, j + 4)Next jsht5.Cells(kk + 1, 5) = sht3.Cells(i, 10) '指导老师sht5.Cells(kk + 1, 6) = sht3.Cells(i, 13) '星期sht5.Cells(kk + 1, 7) = sht3.Cells(i, 14) '上课时间sht5.Cells(kk + 1, 8) = sht3.Cells(i, 15) '上课地点kk = kk + 1End IfNext ki = i + 1'Next iWend
------解决方案--------------------------------------------------------
k = 3: kk = 3
sht5.Activate
'《课程信息》每行和《乐团训练记录》比较，不同就增加
With sht3
For i = 4 To .[F65530].End(xlUp).Row
For k = kk To i - 1
If .Cells(i, 5) <> Cells(k, 1) Or .Cells(i, 6) <> Cells(k, 2) _
Or .Cells(i, 7) <> Cells(k, 3) Or .Cells(i, 8) <> Cells(k, 4) _
Or .Cells(i, 10) = Cells(k, 8) Then
.Range("E" & i & ":H" & i).Copy Range("A" & kk + 1 & ":D" & kk + 1)
Cells(kk + 1, 5) = sht3.Cells(i, 10) '指导老师
.Range("M" & i & ":O" & i).Copy Range("F" & kk + 1 & ":H" & kk + 1) '星期 上课时间 上课地点
kk = kk + 1
End If
Next k
Next i
End With
------解决方案--------------------------------------------------------
第一行的k=3没用，可以去掉。