|
本帖最后由 eking_tang 于 2015-6-12 13:59 编辑
因为其他原因,不能用公式,只能求VBA代码,麻烦各位了,感谢先.
- Sub ddd()
- Dim dic As Object, d As Object, arr, sht As Worksheet
- Dim iRow%, i%, j%, n%, brr()
- Set dic = CreateObject("Scripting.Dictionary")
- Set d = CreateObject("Scripting.Dictionary")
- With ThisWorkbook
- arr = .Sheets("RATE").Range("A1").CurrentRegion
- For i = LBound(arr, 2) + 2 To UBound(arr, 2)
- dic(arr(1, i)) = i '存储列信息
- Next
- For j = LBound(arr) + 1 To UBound(arr)
- d(CInt(arr(j, 1))) = j '存储行信息
- Next
- For Each sht In .Sheets '循环工作表
- If sht.name <> "RATE" Then
- iRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).row
- ReDim brr(1 To iRow - 6)
- For n = 7 To iRow
- brr(n - 6) = arr(d(CInt(sht.Cells(n, "B"))), dic(sht.name))
- Next
- sht.Cells(8, "A").Resize(iRow - 7, 1).ClearContents
- sht.Cells(8, "A").Resize(iRow - 7, 1) = Application.Transpose(brr)
- End If
- Next
- Set dic = Nothing
- Set d = Nothing
- End With
- End Sub
复制代码
|
|