|
- Sub 筛选()
- Dim ok2 As Boolean, ok3 As Boolean
- km = [c4] & [d4] '科目
- c = IIf(km = "上学期语文", 4, IIf(km = "上学期数学", 5, IIf(km = "下学期语文", 6, 7)))
- tj1 = [b4]: tj2 = [e4]: tj3 = [f4]
- c1 = IIf(tj3 = "上学期", 8, 9)
- arr = Sheet1.[a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 3)
- For i = 3 To UBound(arr)
- ok2 = False: ok3 = False
- If tj1 = "" Or arr(i, 3) Like "*" & tj1 & "*" Then '条件1满足
- fs = arr(i, c) '指定科目的分数
- If tj2 = "" Then
- ok2 = True
- Else '条件2为:分数>=(>,<=,<)分数线类型,需分别判断
- For k = 1 To Len(tj2) '找出分数线
- If IsNumeric(Mid(tj2, k, 1)) Then Exit For
- Next
- fsx = Val(Mid(tj2, k)) '分数线
- If InStr(tj2, ">=") > 0 Then
- ok2 = CBool(fs >= fsx)
- ElseIf InStr(tj2, ">") > 0 Then
- ok2 = CBool(fs > fsx)
- ElseIf InStr(tj2, "<=") > 0 Then
- ok2 = CBool(fs <= fsx)
- ElseIf InStr(tj2, "<") > 0 Then
- ok2 = CBool(fs < fsx)
- End If
- End If
- If ok2 = True Then '条件2满足
- If tj3 = "" Then
- If arr(i, 8) = "三好学生" And arr(i, 9) = "三好学生" Then ok3 = True
- Else
- If arr(i, c1) = "三好学生" Then ok3 = True
- End If
- If ok3 Then
- n = n + 1
- brr(n, 1) = arr(i, 3)
- brr(n, 2) = arr(i, c)
- brr(n, 3) = "三好学生"
- End If
- End If
- End If
- Next
- [b8:d1000] = ""
- If n = 0 Then
- MsgBox "查无此信息"
- Else
- [b8].Resize(n, 3) = brr
- End If
- End Sub
复制代码 |
|