# Thread: insert subtotal refred block (2000 sr 1)

1. ## insert subtotal refred block (2000 sr 1)

I have in column M a index value.
I have alreday ordered all record from the column M.

Now with a VBA macro is possible to insert in column N the sub total refred each block of same value in column M.

Example:
into column M is the same block ov laue in range M3:M76
Now, summ the value in range I3:I76
And insert the in N76 the total of range I3:I76

into column M is the same block ov laue in range M77:M81
Now, summ the value in range I77:I81
And insert the in N81 the total of range I77:I81

ecc...

Naturally, If the index is present only one insert into sema cell the total in N, for example in M82 is present only a unique value...

2. ## Re: insert subtotal refred block (2000 sr 1)

1) Convert the text values in column J to numbers.

2) In N3, enter this formula:
<code>
=IF(M3=M4,"",SUMIF(\$M:\$M,M3,\$J:\$J))
</code>
In Italian:
<code>
=SE(M3=M4;"";SUMMA.SE(\$M:\$M;M3;\$J:\$J))
</code>
3) Fill down as far as needed.

3. ## Re: insert subtotal refred block (2000 sr 1)

pERFECT !
And if possible to insert via line in macro similar:
ELENCO.Range("N" + RIGA2) = your formula

4. ## Re: insert subtotal refred block (2000 sr 1)

Try this:
<code>
ELENCO.Range("N" & RIGA2).Formula = "=IF(M" & RIGA & _
"=M" & (RIGA + 1) & ","""",SUMIF(\$M:\$M,M" & RIGA & ",\$J:\$J))"</code>

5. ## Re: insert subtotal refred block (2000 sr 1)

hANS HAVE #NOME in cell N....
This is a part of block code in wich part i insert the line with formulas?:
.....................

DATATAB = Sess0.SCREEN.GetString(3, 35, 2) & "/" & Sess0.SCREEN.GetString(3, 33, 2) & "/" & Sess0.SCREEN.GetString(3, 29, 4)

ELENCO.Range("A" + RIGA2) = DATATAB

If SETTORE < "00" Or SETTORE > "99" Then
RIGA1 = RIGA2 - 1
ELENCO.Range("B" + RIGA2) = ELENCO.Range("B" + RIGA1)
ELENCO.Range("C" + RIGA2) = ELENCO.Range("C" + RIGA1)
Else
ELENCO.Range("B" + RIGA2) = SETTORE
ELENCO.Range("C" + RIGA2) = SPORTELLO
End If

COPE = Trim(Sess0.SCREEN.GetString(I, 3, 8)) ' = COPE
NOMINATIVO = Trim(Sess0.SCREEN.GetString(I, 12, 23)) ' = NOMINATIVO

If COPE = "" Then
RIGA1 = RIGA2 - 1
ELENCO.Range("D" + RIGA2) = ELENCO.Range("D" + RIGA1)
ELENCO.Range("E" + RIGA2) = ELENCO.Range("E" + RIGA1)
Else
ELENCO.Range("D" + RIGA2) = COPE
ELENCO.Range("E" + RIGA2) = NOMINATIVO
End If

ELENCO.Range("F" + RIGA2) = Sess0.SCREEN.GetString(I, 40, 6)
ELENCO.Range("G" + RIGA2) = Trim(Sess0.SCREEN.GetString(I, 47, 15))
ELENCO.Range("H" & RIGA2) = Replace(Sess0.SCREEN.GetString(I, 63, 10), ".", "/")
ELENCO.Range("I" + RIGA2) = Trim(Sess0.SCREEN.GetString(I, 74, 14))
ELENCO.Range("J" + RIGA2) = Trim(Sess0.SCREEN.GetString(I, 90, 14)) * 1
ELENCO.Range("K" + RIGA2) = Trim(Sess0.SCREEN.GetString(I, 106, 27)) ' & Trim(Sess0.SCREEN.GetString(I, 98, 14))

TES_PERC = ELENCO.Range("J" & RIGA2) / ELENCO.Range("I" & RIGA2)

ELENCO.Range("L" & RIGA2) = Format((TES_PERC), "0%")

ELENCO.Range("M" & RIGA2) = ELENCO.Range("C" & RIGA2) & "-" & ELENCO.Range("F" & RIGA2)

RIGA2 = RIGA2 + 1

ELENCO.Range("D1") = RIGA2 - 3
....................

6. ## Re: insert subtotal refred block (2000 sr 1)

Sorry, it should have been

<code>
ELENCO.Range("N" & RIGA2).Formula = "=IF(M" & RIGA2 & _
"=M" & (RIGA2 + 1) & ","""",SUMIF(\$M:\$M,M" & RIGA2 & ",\$J:\$J))"
</code>
(I used RIGA instead of RIGA2 by accident)

7. ## Re: insert subtotal refred block (2000 sr 1)

no worrie.... "you can all", sure
For me naturally:-)
Tks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•