Riferimenti celle

[Ricky0185]

Buonasera, vorrei menzionare in una somma all’interno di una macro i riferimenti a celle e non come fatto con il registratore

Codice: Seleziona tutto

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-213]C:R[-16]C)"

Che sarebbe poi la somma dei valori delle celle D701:D504 filtrate. Se manualmente sostituisco quel SUBTOTAL con “=SOMMA(D504:D701)” mi da #NOME? e nella cella del NOME compare =SOMMA('D504':'D701'). Il problema si acutizza se volessi fare la somma dei valori compresi fra due celle sopra all'ultima cella occupata della colonna D e la cella D2.

Codice: Seleziona tutto

Range(Sheets("Foglio1").Range("D1800").End(xlUp).Offset(-2, 0), Sheets("Libro Cassa").Range("D2"))

Preciso che le celle della colonna D sono in continuo aumento.
Grazie. Saluto
R

[Anthony47]

Qualche esempio:

Codice: Seleziona tutto

ActiveCell.Formula = "=SUM(D504:D701)"             'SUM

ActiveCell.FormulaLocal = "=SOMMa(D504:D701)"      'SOMMA

FAdr=Range(Sheets("Foglio1").Range("D1800").End(xlUp).Offset(-2, 0), Sheets("Libro Cassa").Range("D2")).Address
ActiveCell.FormulaLocal = "=SOMMA(" & fAdr & ")"

[Ricky0185]

Vado a vedeee e te so dì (come dicono qui in romanesco)

[Ricky0185]

La colonna D è filtrata secondo il valore della colonna 5 (E) e perciò ci sono anche molte righe nascoste. Se dopo aver filtrato la colonna applico le tue formule ricevo un totale di tutte le righe, includendo anche quelle nascoste. Dovrei usare la formula =SUBTOTALE(9;D504:D701) che sarebbero le righe non nascoste. Poi se registro il tutto la macro sarebbe

Codice: Seleziona tutto

Range("D800").End(xlUp).Offset(2, 0).Select
    Selection.AutoFilter Field:=5, Criteria1:="e"
    Range("D724").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-220]C:R[-23]C)"
    Range("D724").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.AutoFilter Field:=5
    Application.CutCopyMode = False

ed è proprio quel R[-220]C:R[-23]C) che vorrei apparisse D504:D701 per rendermi più agevole l'inserimento delle celle interessate ai totali. La trasformazione dei totali in valori è proprio perchè a seguire ne devo ricavare altri con filtraggi diversi e diversi riferimenti di celle.

[Ricky0185]

Ho provato anche a ricavarli andando su Dati-Subtotali e successivamente scegliendo la colonna filtrata di cui sommare i valori e quella in base alla quale ordinare. Registrando poi con macro tutta l'operazione. Suggerimento del Grande Ennius.

Codice: Seleziona tutto

Sub Subtotali()
Set zona = Range(Cells(502, 1), Cells(502, 5).End(xlDown))
zona.Select
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
[a723].Select
End Sub

dove 5 è il criterio e 4 la colonna dati da sommare. E funziona, ma con l'unico inconveniente che piazza i subtotali sotto l'ultima cella dell'area filtrata, che ovviamente è diversa a seconda del filtro impostato e quindi mi ritrovo i Subtotali sparsi in giro per la colonna dati.
Finale: li ricavo con la semplice formula =SUBTOTALE(9; .........) così li piazzo dove mi servono.
Ciao e grazie.
R

[Anthony47]

Meno male che hai trovato la soluzione, perche' proprio non ho capito quale era il problema

[Ricky0185]

Il problema era che in una macro il totale dei valori di una colonna filtrata, quindi con righe nascoste, appare come

Codice: Seleziona tutto

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-220]C:R[-23]C)"

(che è la somma dei valori filtrati delle celle D504:D701) mentre vorrei che, sempre nella macro, al posto di R[-220]C:R[-23]C ci fosse D504:D701.