用VBA实现A表筛选多列数据到B表？多谢

www.myexceptions.net  网友分享于：2013-03-06  浏览：29次

VB code
```
'测试《课程信息》有多少行数据
hangsht2 = 3
Do Until sht3.Cells(hangsht2, 6) = ""
hangsht2 = hangsht2 + 1
Loop

k = 3
kk = 3
i = 4

'《课程信息》每行和《乐团训练记录》比较，不同就增加
While i < hangsht2
For k = kk To i - 1

If 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) Then
i = i + 1

Else
For j = 1 To 4
sht5.Cells(kk + 1, j) = sht3.Cells(i, j + 4)
Next j
sht5.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 + 1
End If
Next k
i = i + 1

'Next i
Wend

```

------解决方案--------------------
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

------解决方案--------------------