第51讲-求不及格学生名单和成绩-作业1讲解

Sub 填写不及格学生成绩()
    Dim i As Long, j As Long, pos As Long, col As Long
    pos = 3
    col = Range("k1").Column
    For i = 4 To 8
        
        For j = 3 To Range("a2").CurrentRegion.Rows.Count
            If Cells(j, i) < 60 Then
                Cells(pos, col) = Cells(j, 1)
                Cells(pos, col + 1) = Cells(j, 3)
                Cells(pos, col + 2) = Cells(j, i)
                pos = pos + 1
            End If
        Next
        pos = 3
        col = col + 3
    Next
End Sub

Sub 计算结果()
    '如果total 没赋值它就是0
    '利用total 如果是0 那么就一定是被减数，把当前单元格的数值赋给它
    Dim total As Long, sum As Long, i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row + 1
        If total = 0 Then
            total = Range("a" & i)
        ElseIf Range("a" & i) <> "" Then 
        '如果total 不是0，而且当前的单元格有数值
        '就说明这是要被减掉的部分
            sum = sum + Range("a" & i)
        Else '如果碰到了空单元格，那么就是要填入结果了，注意再把total 清0 因为下一个单元格会是被减数了
            Range("a" & i) = total - sum
            total = 0
            sum = 0
        End If

    Next
End Sub