Problem with macro that performs dynamic sums and noncontinuous intervals

Question

Problem with macro that performs dynamic sums and noncontinuous intervals

Navigation

#1 by (1 votes)
#2 by (-1 votes)

1

I have a worksheet where column A has following values values (1,2,4,6,8,10), the fields with value 1 must be the sum of all groups 2 below it until the next value with field 1, and group 2 should be the sum of all values from group 4 below it to the next cell with value 2, and so on up to group 8, which will be the sum of all values 10 below it .

I have already created a structure for all values of 8 to be filled, but the totals from 1 to 6, as they are not continuous, I can not add.

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

vba excel-vba

asked by anonymous 26.07.2017 / 16:41

2 answers

Google API HeatMap Listing "Client" directories with PHP - Error when directory name has accent

score 1 · Answer 1

Try to add up the values as you count them.

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

score -1 · Answer 2

Many thanks Felipe, as the database has several columns, I thought of leaving the cell filled as formula, so that it would be easy to replicate to the other columns of the table, in the way you mentioned I would have to replicate the padding ara many columns, which would take a long time.

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

The above formula fills the cells with a sum formula, then my macro copies the same formula and drags it to the other columns.