本帖最后由 独奏 于 2014-3-4 19:12 编辑
新手练手,请先保存好副本
忘记了说一下,我把合并的单元格都拆分了并填上相应的序号和名称- Sub text()
- Dim br, arr, brr, crr(), wb, r, c, x, y
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\表1.xls")
- With wb
- arr = .Sheets("sheet1").Range("a1").CurrentRegion
- br = Workbooks("表2.xls").Sheets("sheet1").Range("a1:j1")
- brr = Workbooks("表2.xls").Sheets("sheet1").Range("a1").CurrentRegion
- For r = 2 To UBound(arr)
- For x = 2 To UBound(brr)
- If brr(x, 2) = arr(r, 2) Then
- n = n + 1
- ReDim Preserve crr(1 To 10, 1 To n)
- For y = 1 To UBound(brr, 2)
- crr(y, n) = brr(x, y)
- Next
- End If
- Next
- Next
- End With
- Workbooks("表2.xls").Sheets("sheet2").Range("a1").Resize(, 10) = br
- Workbooks("表2.xls").Sheets("sheet2").Range("a2").Resize(n, 10) = Application.Transpose(crr)
- set arr=nothing
- set brr=nothing
- erase crr
- End Sub
复制代码 |