Dim clmn As String
Dim clmn_cnt As Integer
Dim j As Integer
Dim i As Integer
For i = 1 To 20
For clmn_cnt = 4 To 20 And 22
clmn = Split(Cells(1, clmn_cnt).Address, "$")(1)
Cells(1, clmn_cnt).Value = clmn
If Cells(i, 3).Value = 3 And IsNumeric(Cells(i, 3)) Then
j = i
txt = ""
Do While IsNumeric(Cells(j + 1, 3)) And (Cells(j + 1, 3).Value = 4 Or Cells(j + 1, 3).Value = 5)
If Cells(j + 1, 3).Value = 4 Then
txt = txt & clmn & j + 1 & ","
End If
j = j + 1
Loop
If Len(txt) > 0 Then
txt = Left(txt, Len(txt) - 1)
End If
If Len(txt) > 0 Then
Cells(i, clmn_cnt).Formula = "=SUM(" & txt & ")"
Else
Cells(i, 10).Value = "0"
End If
End If
Next clmn_cnt
Next i
End Sub
|