Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •