|
Sub step1() 'clear yellow area
Application.ScreenUpdating = False
Dim rng As Range
For i = 1 To 150 Step 2
If i = 1 Then
Set rng = Range(Cells(4, i + 5), Cells(63, i + 5))
Else
Set rng = Application.Union(rng, Range(Cells(4, i + 5), Cells(63, i + 5)))
End If
Next
rng.Select
Selection.ClearContents
Application.ScreenUpdating = True
End Sub
Sub step2() 'calculate yellow area sum
ar = Sheet1.Range("A1").CurrentRegion
Set B = Sheet2.[e2:ab2]
R = Sheet2.[A65000].End(3).Row
'---------------------------------
For i = 1 To B.Cells.Count Step 2
For j = 1 To R - 3
C1 = B.Cells(i)
C2 = Sheet2.Cells(j + 3, 1)
C3 = Sheet2.Cells(j + 3, 2)
B.Cells(i).Offset(j + 1).Offset(, 1).Value = CCheck(ar, C1, C2, C3, i, j)
Next
Next
End Sub
Function CCheck(ar, C1, C2, C3, i, j)
For k = 1 To UBound(ar)
If ar(k, 1) = C1 And ar(k, 2) = C2 And ar(k, 3) = C3 Then
S = S + ar(k, 4)
End If
Next
CCheck = S
End Function
|
评分
-
1
查看全部评分
-
|