|
- Option Explicit
- Sub demo()
- Dim arr, brr, crr, drr, a As Integer, b As Integer
- arr = Sheet1.Range("b2:b" & Sheet1.Cells(Sheet1.Rows.Count, "b").End(xlUp).Row) '编号
- brr = Sheet1.Range("c2:c" & Sheet1.Cells(Sheet1.Rows.Count, "b").End(xlUp).Row) '名字
- crr = Sheet1.Range("d2:d" & Sheet1.Cells(Sheet1.Rows.Count, "b").End(xlUp).Row) '评级
- drr = Sheet1.Range("a2:a" & Sheet1.Cells(Sheet1.Rows.Count, "b").End(xlUp).Row) '日期
- 'Dim d1 As New Dictionary, d2 As New Dictionary
- Dim d1, d2
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Dim i As Long, j As Long
- For i = 1 To UBound(brr)
- d1(drr(i, 1)) = ""
- d2(arr(i, 1)) = ""
- Next i
- With Sheet3.Range("a1")
- .Value = "编号"
- .Offset(0, 1) = "名字"
- .Offset(1, 0).Resize(d2.Count) = Application.Transpose(d2.Keys)
- .Offset(0, 2).Resize(, d1.Count) = d1.Keys
- End With
- Sheet3.Range("1:1").NumberFormat = "M月"
- With Sheet3
- .Range("b2").Formula = "=VLOOKUP(A2,Sheet数据!B:C,2,0)"
- .Range("b2:b" & d2.Count + 1).FillDown
- .Range("c2").FormulaArray = "=INDEX(Sheet数据!$D:$D,MATCH(1,(Sheet数据!$A:$A=C$1)*(Sheet数据!$B:$B=$A2),0))"
- .Range("c2").Copy
- .Range("c2").Resize(d2.Count, d1.Count).PasteSpecial (xlPasteFormulas)
- End With
- MsgBox "已完成"
- End Sub
复制代码
|
|