|
B22我不知道呀交作业!
本帖最后由 我不知道呀 于 2012-6-15 20:41 编辑
第一题代码:
Sub aa2()
Dim d As New Dictionary
Dim d1 As New Dictionary
Dim i As Long, j As Long, z As Long, z1 As Long
Dim arr()
Dim arr1
For j = 2 To Sheet1.Range("d65536").End(xlUp).Row
d1(Cells(j, 5).Value) = ""
Next j
For i = 2 To Sheet1.Range("a65536").End(xlUp).Row
d(Cells(i, 1).Value) = ""
Next i
For i = 2 To Sheet1.Range("a65536").End(xlUp).Row
If d1.Exists(Cells(i, 1).Value) = False Then
d1(Cells(i, 1).Value) = d1.Count + 1
Cells(d1(Cells(i, 1).Value) + 1, 4) = d1(Cells(i, 1).Value)
Cells(d1(Cells(i, 1).Value) + 1, 4).Font.Bold = True
Cells(d1(Cells(i, 1).Value) + 1, 5) = d1.Keys(d1(Cells(i, 1).Value) - 1)
Cells(d1(Cells(i, 1).Value) + 1, 5).Font.Bold = True
End If
Next i
ReDim arr(1 To d.Count, 1 To 3)
d.RemoveAll
For i = 2 To Sheet1.Range("a65536").End(xlUp).Row
If d.Exists(Cells(i, 1).Value) = False Then
d(Cells(i, 1).Value) = d.Count + 1
arr(d(Cells(i, 1).Value), 1) = d(Cells(i, 1).Value)
arr(d(Cells(i, 1).Value), 2) = d.Keys(d(Cells(i, 1).Value) - 1)
arr(d(Cells(i, 1).Value), 3) = Cells(i, 2)
Else
arr(d(Cells(i, 1).Value), 3) = arr(d(Cells(i, 1).Value), 3) + Cells(i, 2)
End If
Next i
arr1 = Range("d2:F" & d.Count + 1)
For z = 1 To d.Count
For z1 = 1 To Sheet1.Range("d65536").End(xlUp).Row - 1
If arr(z, 2) = arr1(z1, 2) Then
arr1(z1, 3) = arr(z, 3)
End If
Next z1
Next z
Range("D2").Resize(d.Count, 3) = arr1
End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------
第二题代码:
Sub bb()
Dim d As New Dictionary
Dim x As Long
Dim r As Integer
Dim mergestr As String
Dim MergeCot As Integer
Dim arr()
Dim i As Integer
Dim j As Integer
With Sheet2
r = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim arr(1 To r - 1, 1 To 1)
For i = 2 To r
mergestr = .Cells(i, 1).Value
MergeCot = .Cells(i, 1).MergeArea.Count
For j = 1 To MergeCot
arr(i - 1, 1) = mergestr
i = i + 1
Next j
i = i - 1
Next i
End With
Sheet2.Range("E2:F1000").ClearContents
For x = 2 To 111
If d.Exists(arr(x - 1, 1)) = False Then
d(arr(x - 1, 1)) = d.Count + 2
Cells(d(arr(x - 1, 1)), 5) = arr(x - 1, 1)
If Sheet2.Cells(x, 2) >= 100 And Sheet2.Cells(x, 2) <= 400 Then Sheet2.Cells(d(arr(x - 1, 1)), 6) = Sheet2.Cells(x, 2)
Else
If Sheet2.Cells(x, 2) >= 100 And Sheet2.Cells(x, 2) <= 400 Then
Sheet2.Cells(d(arr(x - 1, 1)), 6) = Sheet2.Cells(d(arr(x - 1, 1)), 6) + Sheet2.Cells(x, 2)
End If
End If
Next x
End Sub
|
评分
-
查看全部评分
|