sommase indiretto comportamento anomalo

Hai un problema con Excel?

Nuova discussione

somma.se indiretto, comportamento anomalo
di Putto data: 21/07/2015 15:41:32
buongiorno,

dopo i vostri consigli e suggerimenti di qualche giorno fa sono arrivato quasi a finire le mie macro, ma quando uso la stringa somma.se "altre colonne" sulla prima colonna dei TOT. mi conteggia tutte le colonne di riferimento, se la uso sulla TOT. di mezzo per esempio mi conteggia solo la prima colonna dei valori, il conteggio "ultima colonna" mi pare funzioni correttamente.

mi aiutate a capire perchè il somma.se "altre colonne" si comporta in maniera differente?

vi allego il file xls ti test e il codice con tutte le macro create

Sub Crea_pulsanti()
'
' Crea_Pulsanti Macro
'
' Scelta rapida da tastiera: CTRL+p
'
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 354, 0.75, 62.2, 15). _
        Select
        Selection.OnAction = "PERSONAL.XLSB!spazio_nomefile"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Nome file"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignLeft
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
'        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    
    Selection.ShapeRange.ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
    Selection.Placement = xlFreeFloating
    Selection.PrintObject = msoFalse
    Selection.ShapeRange.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
    Selection.Placement = xlFreeFloating
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 434.25, 0.75, 68.25, 24). _
        Select
        Selection.OnAction = "PERSONAL.XLSB!Ultima_colonna"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Ultima Colonna"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 14). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignLeft
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 14).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
'        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
                With Selection.ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent6
                .ForeColor.TintAndShade = 0
'                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
    Selection.ShapeRange.ScaleWidth 1.2087912088, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0818181818, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
    Selection.PrintObject = msoFalse
    Selection.Placement = xlFreeFloating
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 542.25, 0.75, 97.5, 24.75 _
        ).Select
        Selection.OnAction = "PERSONAL.XLSB!Altre_colonne"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Altre Colonne"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 13). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignLeft
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 13).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
'        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
            With Selection.ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent6
                .ForeColor.TintAndShade = 0
'                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
    Selection.ShapeRange.ScaleWidth 0.8076923077, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
    Selection.PrintObject = msoFalse
    Selection.Placement = xlFreeFloating
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 636, 0.75, 55.5, 24). _
        Select
        Selection.OnAction = "PERSONAL.XLSB!Totali"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Totali"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignLeft
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
'        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
            With Selection.ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent3
                .ForeColor.TintAndShade = 0
'                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With

    Selection.ShapeRange.ScaleWidth 0.7567567568, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
    Selection.PrintObject = msoFalse
    Selection.Placement = xlFreeFloating
End Sub

Sub spazio_nomefile()
'
' nomefile Macro
' Scelta rapida da tastiera: CTRL+q
' pulisco celle unite e inserisco spazio

    Cells.Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        
        Cells.Select
        Selection.Columns.AutoFit
        Selection.Rows.AutoFit
        Selection.UnMerge
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown
        Selection.Insert Shift:=xlDown
    ' inserisco il nome del file e carattere
        Range("A1").Select
        Selection.Font.Bold = True
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
        Range("A1").Select
        ActiveCell.FormulaR1C1 = _
            "=MID(CELL(""FILENAME"",R[79]C[7]),FIND(""["",CELL(""FILENAME"",R[79]C[7]))+5,FIND(""]"",CELL(""FILENAME"",R[79]C[7]))-FIND(""["",CELL(""FILENAME"",R[79]C[7]))-11)& "" - ""& (MID(R[2]C,9,LEN(R[2]C)))"
        Range("A2").Select
        
    'eseguo la sub maiuscole
    'creo una variabile CD come OGGETTO
    Dim CD As Object
    'creo delle variabili stringa temporanee
    Dim temp As String
    'seleziono una zona del foglio dove voglio operare
    Set zona = Range("a1")
    'in tale zona eseguo un ciclo cella per cella
    'ne estraggo la prima lettera che converto in maiuscola
    'prendo le residue lettere e le trasformo in minuscole
    For Each CD In zona
    temp = CD.Value
    temp1 = UCase(Left$(temp, 1))
    'la riga sotto se voglio solo la prima maiuscola metto LCase
    temp2 = UCase$(Mid$(temp, 2, Len(temp) - 1))
    temp = temp1 & temp2
    CD.Value = temp
    Next
    
End Sub


Sub Totali()
'
' Totali Macro
'
' Scelta rapida da tastiera: CTRL+t
'
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
   ' Selection.UnMerge
'    ActiveCell.Offset(-1, 0).Range("A1").Select
'    Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
'       xlFillDefault
    ActiveCell.Range("A1:A2").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(R1C1:R100C1,""*"",C:C)"
'    ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
    
    ActiveCell.Select
'    ActiveCell.Offset(0, 1).Range("A1").Select
'    Selection.AutoFill Destination:=ActiveCell.Range("A1:E1"), Type:= _
'        xlFillDefault
'    ActiveCell.Range("A1:E1").Select

' faccio il controllo del colore e replico i totali
    Do Until ActiveCell.Interior.ColorIndex < 1
    
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    Loop
    
        ActiveCell.Select
        Application.CutCopyMode = False
        Selection.Delete



End Sub

Sub Ultima_colonna()
'
' Ultima_colonna Macro
'
' Scelta rapida da tastiera: CTRL+e
    Do Until ActiveCell.Value = ""
    
        ActiveCell.FormulaR1C1 = _
        "=SUMIF(INDIRECT(ADDRESS(3,COLUMN()+1)&"":""&ADDRESS(3,COUNTA(R3))),LEFT(INDIRECT(ADDRESS(3,COLUMN()+1)),2)&""*""," & Chr(10) & "INDIRECT(ADDRESS(ROW(),COLUMN()+1)&"":""&ADDRESS(ROW(),COUNTA(R3)+2)))"
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
        ActiveCell.Offset(-1, 0).Range("A1").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
        xlFillDefault

End Sub
Sub Altre_colonne()
'
' Altre_colonne Macro
'
' Scelta rapida da tastiera: CTRL+w
'
    Do Until ActiveCell.Value = ""

        ActiveCell.FormulaR1C1 = _
        "=SUMIF(INDIRECT(ADDRESS(3,COLUMN()+1)&"":""&ADDRESS(3,MATCH(""TOT*"",INDIRECT(ADDRESS(3,COLUMN()+1)&"":""&ADDRESS(3,COUNTA(R3))),0)+2))," & Chr(10) & "LEFT(INDIRECT(ADDRESS(3,COLUMN()+1)),2)&""*"",INDIRECT(ADDRESS(ROW(),COLUMN()+1)&"":""&ADDRESS(ROW(),MATCH(""TOT*""," & Chr(10) & "INDIRECT(ADDRESS(3,COLUMN()+1)&"":""&ADDRESS(3,COUNTA(R3))),0)+2)))"
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
        ActiveCell.Offset(-1, 0).Range("A1").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
        xlFillDefault

End Sub




Sub formattazione_calcolo()
'
' formattazione Macro
'
' Scelta rapida da tastiera: CTRL+y
' già integrata in quella dei totali utile solo per TEST

    Do Until ActiveCell.Interior.ColorIndex < 1
    
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        
        Selection.Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    Loop
    
        ActiveCell.Select
        Application.CutCopyMode = False
        Selection.Delete
End Sub

di cromagno data: 22/07/2015 06:37:59
Ciao,
nel file allegato ci sono solo le formule, quindi ho controllato quelle.
L'errore sta quando aggiungi "+2" per il riferimento colonna che ti rende l'indirizzo del range da sommare.
Per la prima colonna quel "+2" va bene, in quanto ci sono effettivamente 2 colonne prima della prima colonna "TOT", ma per le altre quel "+2" deve aumentare in base alla posizione.
Questo lo puoi fare sostituendo il "+2" con "+RIF.COLONNA()-1".
Quindi, la formula da usare per tutte le colonne TOT (tranne che per l'ultima, anche se non ho controllato) è:
=SOMMA.SE(INDIRETTO(INDIRIZZO(3;RIF.COLONNA()+1)&":"&INDIRIZZO(3;CONFRONTA("TOT*";INDIRETTO(INDIRIZZO(3;RIF.COLONNA()+1)&":"&INDIRIZZO(3;CONTA.VALORI($3:$3)));0)+RIF.COLONNA()-1));
SINISTRA(INDIRETTO(INDIRIZZO(3;RIF.COLONNA()+1));2)&"*";INDIRETTO(INDIRIZZO(RIF.RIGA();RIF.COLONNA()+1)&":"&INDIRIZZO(RIF.RIGA();CONFRONTA("TOT*";
INDIRETTO(INDIRIZZO(3;RIF.COLONNA()+1)&":"&INDIRIZZO(3;CONTA.VALORI($3:$3)));0)+RIF.COLONNA()-1)))

Ti riallego il file con la formula già inserita...
di cromagno data: 22/07/2015 08:09:41
Se non ricordo male, ti avevo proposto io la prima formula, quindi... pardon!
In effetti, l'avevo provata solo nel file da te allegato dove c'erano solamente due colonne TOT.
di Putto data: 22/07/2015 10:30:25
si la formula era la tua e grazie della nuova dritta,

ora provo anche con le nuove formule da te corrette, ma ho visto che se uso la formula dell'ultima riga su tutte le colonne TOT ad eccezione della prima e sulla prima quella con riferimento +2 mi pare funzioni ugualmente.

ora faccio ancora qualche altro test aggiungendo e togliendo colonne dei dati per simulare meglio le varie casistiche :)

Aggiornamenti:

Croma grazie mille ora va che è una meraviglia :) ho aggiunto qualche altra formattazione e la pulizia dei pulsanti a fine calcolo dei totali così i miei "utonti" non fanno piu manco quella fatica

chiudo con risolto

Vuoi Approfondire?