|
楼主 |
发表于 2017-6-18 20:40
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 aman1516 于 2017-6-18 21:13 编辑
勉强拼凑出来的:
- Sub p()
- Dim arr, brr, crr, i, j, k, m, n, r, c, p, sr, s, a
- On Error Resume Next
- r = Range("A65536").End(xlUp).Row
- arr = Range("A3:N" & r)
- sr = Array("二扇", "三扇", "2+1扇", "四扇", "4+2扇", "六扇")
- p = Range("E2").Value
- c = 0: k = 0
- For s = 0 To UBound(sr)
- If InStr(p, sr(s)) Then
- c = s + 9
- Exit For
- End If
- Next
- ReDim brr(1 To UBound(arr), 1 To 7)
- For i = 2 To UBound(arr)
- If arr(i, 2) = "" Then
- k = k + 1
- If arr(i, 3) <> "" Then
- If InStr(p, arr(i, 3)) > 0 and arr(i, 3) = "窗" And arr(i, 5) = arr(i - 1, 5) Then
- k = k - 1
- Else
- If InStr(p, arr(i, 3)) = 0 Then
- k = k - 1
- GoTo 100
- End If
- End If
- End If
- brr(k, 1) = arr(i, 1)
- For j = 4 To 8
- brr(k, j - 2) = arr(i, j)
- Next
- brr(k, 7) = arr(i, c)
- Else
- If InStr(p, arr(i, 2)) > 0 Then
- k = k + 1
- If arr(i, 3) = "窗" And arr(i, 5) = arr(i - 1, 5) Then
- k = k - 1
- End If
- brr(k, 1) = arr(i, 1)
- For j = 4 To 8
- brr(k, j - 2) = arr(i, j)
- Next
- brr(k, 7) = arr(i, c)
- End If
- End If
- 100:
- Next
- Range("C31:I50").ClearContents
- Range("C31").Resize(k, 7) = brr
- End Sub
复制代码
只是觉得方法很笨,所以求优化 |
|