|
发表于 2017-9-12 16:46
|
显示全部楼层
本楼为最佳答案
结果显示在sheet5,sheet6,sheet7
- Sub tt()
- arr = Sheet1.[a2].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- '1 从sheet1中筛选出sheet2(只要列中有一个值大于等于1,就符合条件)
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, j) >= 1 Then
- n = n + 1
- d(Val(arr(i, 1))) = d(Val(arr(i, 1))) & "," & n
- For jj = 1 To UBound(arr, 2)
- brr(n, jj) = arr(i, jj)
- Next
- Exit For
- End If
- Next
- Next
-
- '2、从sheet2中筛选出sheet3(A列数值相差10的)
- ReDim crr(1 To n, 1 To UBound(arr, 2))
- For i = 1 To UBound(brr)
- x = Val(brr(i, 1))
- If d.exists(x + 10) Then s = s & d(x) & d(x + 10)
- Next
- d.RemoveAll
- srr = Split(s, ",")
- For k = 1 To UBound(srr)
- d(srr(k)) = ""
- Next
- For Each i In d.keys
- m = m + 1
- For j = 1 To UBound(brr, 2)
- crr(m, j) = brr(i, j)
- Next
- Next
-
- '3、从sheet2中筛选出sheet4(不满足2的条件,但是列中的数值比较大,如大于10)
- ReDim drr(1 To n, 1 To UBound(arr, 2))
- For i = 1 To UBound(brr)
- If Not d.exists(i) Then
- For j = 1 To UBound(brr, 2)
- If brr(i, j) >= 10 Then
- p = p + 1
- For jj = 1 To UBound(arr, 2)
- drr(p, jj) = brr(i, jj)
- Next
- Exit For
- End If
- Next
- End If
- Next
-
- Sheet5.Cells.Clear: Sheet6.Cells.Clear: Sheet7.Cells.Clear
- If n > 0 Then Sheet5.[a1].Resize(n, UBound(arr, 2)) = brr
- If m > 0 Then Sheet6.[a1].Resize(m, UBound(arr, 2)) = crr
- If p > 0 Then Sheet7.[a1].Resize(p, UBound(arr, 2)) = drr
- End Sub
复制代码 |
|