|
本帖最后由 一指禅62 于 2019-10-15 14:11 编辑
VBA的
- Sub 筛选空堂教师()
- On Error GoTo Err1
- Dim d As Object, Rng As Range, C%, R%, key$
- Set d = CreateObject("Scripting.Dictionary")
- With Sheet3
- Rem 获取本班教师
- For Each Rng In Sheet4.Range("A4:A13").Find(.[J2], lookat:=xlWhole).Offset(0, 1).Resize(1, 20)
- If Trim(Rng.Value) <> "" Then d(Trim(Rng.Value)) = ""
- Next
- Rem 查询当节课教师
- C = IIf(.[J4] = "早自习", 0, .[J4])
- R = Sheet1.Range("A5:A14").Find(.[J2], lookat:=xlPart).Row - 2
- key = Trim(Sheet1.Range("B2:BD2").Find(.[J3], lookat:=xlPart).Offset(R, 0).Offset(0, C)) '当堂教师
- If d.Exists(key) Then d.Remove (key) '移除这名教师
- .Range("J5:J300") = ""
- .Range("J5").Resize(d.Count, 1) = WorksheetFunction.Transpose(d.keys) '输出空堂教师
- End With
- Exit Sub
- Err1:
- MsgBox "错误提示:" & Err.Description
- End Sub
复制代码
|
|