guolianjd 发表于 2016-9-10 21:47
不好意思是我把数据复制重复了,也就是说把2表新增的人插入1表相同的户主的名下,附件已重新上传
既然忽略第2个问题,那就以示例结果为准,
以下代码楼主测试:- Sub YuBa()
- Dim d As Object, arr(1), brr(1 To 10000, 1 To 8), s$, m%, n%, i%, j%
- Set d = CreateObject("scripting.dictionary")
- arr(0) = Sheet2.UsedRange
- With Sheet1
- arr(1) = .Range("a2").CurrentRegion
- For i = 1 To UBound(arr(0))
- If arr(0)(i, 4) = "户主" Then s = arr(0)(i, 5)
- If arr(0)(i, 8) = "新增" Then
- If d.exists(s) Then
- d(s) = d(s) & " " & i
- Else
- d(s) = i
- End If
- End If
- Next
- For i = 1 To UBound(arr(1))
- n = n + 1
- If arr(1)(i, 4) = "户主" Then s = arr(1)(i, 5)
- For j = 1 To 8
- brr(n, j) = arr(1)(i, j)
- Next
- If i < UBound(arr(1)) Then
- If arr(1)(i + 1, 4) = "户主" Then
- If d.exists(s) Then
- ar = Split(d(s))
- For m = 0 To UBound(ar)
- n = n + 1
- For j = 1 To 8
- brr(n, j) = arr(0)(ar(m), j)
- Next
- Next
- End If
- End If
- End If
- Next
- .[t2:aa999] = ""
- .[t2].Resize(n, 8) = brr
- End With
- End Sub
复制代码
工作表 (2).rar
(20.73 KB, 下载次数: 5)
|