For i = 1 To b
    If balancete.Cells(i + 1, 1) = 10 Then
        cont_10 = cont_10 + 1
    ElseIf balancete.Cells(i + 1, 1) <> 10 Then
        If balancete.Cells(i, 1) = 10 Then
           balancete.Cells(i - cont_10, 4) = "=SOMA(D" & i + 1 - cont_10 & ":D" & i & ")"
           cont_10 = 0
        End If
    Else
        i = i + 1
    End If
Next i

Dim soma As Double
soma = 0

For i = 1 To b
    If balancete.Cells(i + 1, 1) = 10 Then

        cont_10 = cont_10 + 1
        soma = soma + balancete.Cells(i + 1, 1).Value 'soma os valores a medida com que são localizados

    ElseIf balancete.Cells(i + 1, 1) <> 10 Then
        If balancete.Cells(i, 1) = 10 Then
           balancete.Cells(i - cont_10, 4) = soma 'preenche a celular com o valor total
           cont_10 = 0
        End If
    Else
        i = i + 1
    End If
Next i