|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub huiz()
Dim i, irow
Dim ar
Dim d1, d2, d3, d4, d5, d6 As Object
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")
Set d4 = CreateObject("scripting.dictionary")
Set d5 = CreateObject("scripting.dictionary")
Set d6 = CreateObject("scripting.dictionary")
irow = Sheets("龙凤煤矿资产盘点表").[a65536].End(xlUp).Row
ar = Sheets("龙凤煤矿资产盘点表").Range("a1:g" & irow)
For i = 4 To irow
If ar(i, 6) <> "" Then
d3(Left(ar(i, 2), 8)) = d3(Left(ar(i, 2), 8)) + ar(i, 6)
d2(Left(ar(i, 2), 6)) = d2(Left(ar(i, 2), 6)) + ar(i, 6)
d1(Left(ar(i, 2), 4)) = d1(Left(ar(i, 2), 4)) + ar(i, 6)
End If
If ar(i, 7) <> "" Then
d6(Left(ar(i, 2), 8)) = d6(Left(ar(i, 2), 8)) + ar(i, 7)
d5(Left(ar(i, 2), 6)) = d5(Left(ar(i, 2), 6)) + ar(i, 7)
d4(Left(ar(i, 2), 4)) = d4(Left(ar(i, 2), 4)) + ar(i, 7)
End If
Next
With Sheets("龙凤煤矿资产盘点表")
For i = 4 To irow
If .Cells(i, 6) = "" And Len(.Cells(i, 2)) <= 8 Then
If Len(.Cells(i, 2)) = 4 Then
.Cells(i, 6) = d1(.Cells(i, 2).Value)
Else
If Len(.Cells(i, 2)) = 6 Then
.Cells(i, 6) = d2(.Cells(i, 2).Value)
Else
.Cells(i, 6) = d3(.Cells(i, 2).Value)
End If
End If
End If
If .Cells(i, 7) = "" Or Len(.Cells(i, 2)) <= 8 Then
If Len(.Cells(i, 2)) = 4 Then
.Cells(i, 7) = d4(.Cells(i, 2).Value)
Else
If Len(.Cells(i, 2)) = 6 Then
.Cells(i, 7) = d5(.Cells(i, 2).Value)
Else
.Cells(i, 7) = d6(.Cells(i, 2).Value)
End If
End If
End If
Next
End With
MsgBox "ok"
End Sub |
|