|
发表于 2014-2-28 08:36
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click() '数组法
- Dim arr, brr(), f(1 To 17) As Boolean, t, i&, j&, m&, lr&, lc&
- t = Range("A2:S2")
- For i = 1 To 17 Step 1
- If Len(t(1, i)) Then f(i) = True
- Next
- If Not (f(1) Or f(3) Or f(5)) Then Exit Sub
- With Sheets("出入库汇总表")
- arr = .Range("A2").CurrentRegion
- lr = .Range("b" & Rows.Count).End(xlUp).Row
- lc = UBound(arr, 2)
- ReDim brr(1 To lr, 1 To lc) '重新定义的数组
- If Me.CheckBox1.Value Then
- For i = 2 To lr
- If ((f(1) And (arr(i, 2) = t(1, 1))) Or Not f(1)) _
- And ((f(3) And (arr(i, 4) = t(1, 3))) Or Not f(3)) _
- And ((f(5) And (arr(i, 17) = t(1, 5))) Or Not f(5)) Then
- m = m + 1
- For j = 1 To lc
- brr(m, j) = arr(i, j)
- Next
- End If
- Next
- Else
- For i = 2 To lr
- If ((f(1) And InStr(arr(i, 2), t(1, 1)) > 0) Or Not f(1)) _
- And ((f(3) And InStr(arr(i, 4), t(1, 3)) > 0) Or Not f(3)) _
- And ((f(5) And InStr(arr(i, 17), t(1, 5)) > 0) Or Not f(5)) Then
- m = m + 1
- For j = 1 To lc
- brr(m, j) = arr(i, j)
- Next
- End If
- Next
- End If
- End With
- Range("A5:S" & Rows.Count).ClearContents
- If m Then [a5].Resize(m, j - 1) = brr
- End Sub
复制代码 |
|