|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Private Sub CommandButton1_Click()
- Dim Arr, i&, mc$, xh$, cj$, n&, x$, y$
- [a5:g500].ClearContents
- mc = [a2].Value: xh = [c2].Value: cj = [e2].Value
- Arr = Sheet1.[a1].CurrentRegion: n = 4
- If mc <> "" Then
- If xh <> "" Then
- If cj <> "" Then
- x = mc & "|" & xh & "|" & cj
- For i = 2 To UBound(Arr)
- y = Arr(i, 2) & "|" & Arr(i, 3) & "|" & Arr(i, 5)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- Else
- x = mc & "|" & xh
- For i = 2 To UBound(Arr)
- y = Arr(i, 2) & "|" & Arr(i, 3)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- End If
- Else
- If cj <> "" Then
- x = mc & "|" & cj
- For i = 2 To UBound(Arr)
- y = Arr(i, 2) & "|" & Arr(i, 5)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- Else
- For i = 2 To UBound(Arr)
- If Arr(i, 2) = mc Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- End If
- End If
- ElseIf xh <> "" Then
- If cj <> "" Then
- x = xh & "|" & cj
- For i = 2 To UBound(Arr)
- y = Arr(i, 3) & "|" & Arr(i, 5)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- Else
- x = xh
- For i = 2 To UBound(Arr)
- y = Arr(i, 3)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- End If
- Else
- If cj <> "" Then
- x = cj
- For i = 2 To UBound(Arr)
- y = Arr(i, 5)
- If x = y Then
- n = n + 1
- Cells(n, 1).Resize(1, UBound(Arr, 2)) = Application.Index(Arr, i, 0)
- End If
- Next
- Else
- MsgBox "查询条件不能全部为空白。 "
- End If
- End If
- End Sub
复制代码 |
|