franc.ciccio
00giovedì 5 novembre 2020 20:15
Ciao a tutti.
Nel workbook allegato "master_05_11_20" ci sono 2 formule per trovare valori tra questo workbook e il wookbook "cartel2" riferite agli anni 2017/2018/2019
=MATR.SOMMA.PRODOTTO(([cartel2.xlsx]Foglio1!$F$2:$F$63000=$A3)*(([cartel2.xlsx]Foglio1!$N$2:$N$63000)=D$2)*([cartel2.xlsx]Foglio1!$AC$2:$AC$63000))
=SOMMA.PIÙ.SE([cartel2.xlsx]Foglio1!$AC:$AC;[cartel2.xlsx]Foglio1!$F:$F;A3;[cartel2.xlsx]Foglio1!$N:$N;$J$2)
è possibile trasformare queste formule in vba?
grazie
fc
rollis13
00giovedì 5 novembre 2020 21:51
Usa il Registratore di Macro per convertirle. Avvia il Registratore / simula una modifica alla cella / e chiudi il Registratore.
In un modulo VBE troverai traccia di quanto dovrai riportare in una macro.
franc.ciccio
00giovedì 5 novembre 2020 22:33
Ho creato le formule con il registratore, solo che le righe per ogni formula e più di 700000 e si inchioda tutto.
Option Explicit
Sub Macro1()
Range("D3:D900000").Select
Range("D3:D900000").Formula = "=SUMPRODUCT(([cartel2.xlsx]Foglio1!R2C6:R630000C6=RC1)*(([cartel2.xlsx]Foglio1!R2C14:R630000C14)=R2C)*([cartel2.xlsx]Foglio1!R2C29:R630000C29))"
'Range("E3").Select
End Sub
Sub Macro2()
Range("F3:F900000").Select
Range("F3:F900000").Formula = "=SUMPRODUCT(([cartel2.xlsx]Foglio1!R2C6:R630000C6=RC1)*(([cartel2.xlsx]Foglio1!R2C14:R630000C14)=R2C)*([cartel2.xlsx]Foglio1!R2C29:R630000C29))"
'Range("G3").Select
End Sub
Sub Macro3()
Range("H3:H900000").Select
Range("H3:H900000").Formula "=SUMPRODUCT(([cartel2.xlsx]Foglio1!R2C6:R630000C6=RC1)*(([cartel2.xlsx]Foglio1!R2C14:R630000C14)=R2C)*([cartel2.xlsx]Foglio1!R2C29:R630000C29))"
'Range("I3").Select
End Sub
'=========================================================================================================================================================
Sub Macro4()
Range("J3:J900000").Select
Range("J3:J900000").Formula = "=SUMIFS([cartel2.xlsx]Foglio1!C29,[cartel2.xlsx]Foglio1!C6,RC[-9],[cartel2.xlsx]Foglio1!C14,R2C10)"
'Range("K3").Select
End Sub
Sub Macro5()
Range("L3:L900000").Select
Range("L3:L900000").Formula "=SUMIFS([cartel2.xlsx]Foglio1!C29,[cartel2.xlsx]Foglio1!C6,RC[-11],[cartel2.xlsx]Foglio1!C14,R2C12)"
'Range("M3").Select
End Sub
Sub Macro6()
Range("N3:N900000").Select
Range("N3:N900000").Formula = "=SUMIFS([cartel2.xlsx]Foglio1!C29,[cartel2.xlsx]Foglio1!C6,RC[-13],[cartel2.xlsx]Foglio1!C14,R2C14)"
'Range("O3").Select
End Sub
è possibile con una macro che incolli i risulati senza le formule?
by sal
00sabato 7 novembre 2020 11:38
Ciao vista la mole dei dati di partenza che credo sia il file Master, a te interessa trovare solamente i dati corrispondenti alla colonna "A" cioè la somma di tutto l'anno. perche ci sono colonne verdi e gialle dello stesso anno?
forse le colonne gialle sarebbero il risultato della macro
se è cosi ti preparo la macro che fa il lavoro
fai sapere, ciao By sal (8-D
franc.ciccio
00sabato 7 novembre 2020 11:46
Ciao sal.
Nel foglio master colonne verdi devono essere inseriti i valori degli
anni divisi del foglio cartel.
Le colonne gialle sono altri valori che poi vengono inserite da una query a parte.
La macro deve inserire i valori nelle colonne verdi.
Grazie
ABCDEF@Excel
00sabato 7 novembre 2020 12:16
Ormai l'ho fatto ed allego
Ti allego il file "Funzionante", e l'altro file cartel2.xlsx (tutti due aperti)
Se devi sommare usa Foglio1, per 900.000 righe ci metterà 5/10/15 minuti
Se devi sommare per ogni anno, usa Foglio2 non sò quanto impiegherà
Ps. Non ci sono formule
Causa errore, sostituire... (SumIfs<<<>>>SumIf)
Trova1...rg = WorksheetFunction.SumIf(sh2.Range("F2:F" & Ur2), "=" & sh1.Cells(X, 1), sh2.Range("AC2:AC" & Ur2))
Trova2...rg = WorksheetFunction.SumIf(sh2.Range("AD2:AD" & Ur2), "=" & sh1.Cells(X, 1) & "_" & sh1.Cells(2, Y), sh2.Range("AC2:AC" & Ur2))
franc.ciccio
00sabato 7 novembre 2020 21:24
Grazie ABCDEF ma con moltissime righe mi sa che poi si blocchi tutto.
Ora ho provato con 5/6mila righe ed è lento, con più di 500/600 mila non so.
fc
ABCDEF@Excel
00domenica 8 novembre 2020 00:29
Inserisci queste righe prima e dopo, deve migliorare di sicuro
Application.ScreenUpdating = False
Application.Calculation = xlManual
>>>Ur1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
...
...
...
>>>MsgBox "Fatto"
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
franc.ciccio
00domenica 8 novembre 2020 08:21
Ciao ABCDEF ho provato qui a casa con excel2007 con 130000 righe,
si è bloccato tutto.
Provo domani in uffico con office365
Grazie
ABCDEF@Excel
00domenica 8 novembre 2020 11:20
Sicuro d'aver dei dati omogenei?
Sono curioso, allega l'originale cartel2.xlsx (casomai usa https://www.filedropper.com/)
Se ci sono dati personali, fai una copia delle colonne F-N-AC
franc.ciccio
00domenica 8 novembre 2020 13:02
Ciao, al file carte2_A ho aggiunto fino a 20000 righe.
Ho dimenticato nel file "funzionante" allegato di cambiare
Dim wk2 As Workbook: Set wk2 = Workbooks("cartel2_A.xlsx")
fc
tanimon
00domenica 8 novembre 2020 13:25
ciao,
per evitare di far rileggere tutto il thread,
allegherei anche i 2 file interessati nella loro ultima versione.
Cosa ne pensi?
ciao
Frank
franc.ciccio
00domenica 8 novembre 2020 19:57
Ops, dimenticato di inserire l' allegato.
Ormai con sto coronavirus sono in confusione...
fc
ABCDEF@Excel
00domenica 8 novembre 2020 21:35
Con 20.000 righe Excel non si è bloccato, foglio2 tempo = 2 minuti
Nel post 07/11/2020 12:16... (sommare per ogni anno, usa Foglio2 e sostituire)
Trova2...rg = WorksheetFunction.SumIf(sh2.Range("AD2:AD" & Ur2), ...
Purtroppo mi sono dimenticato di farTi sostituire pure>>>Ur2 = sh2.Range("A" & Rows.Count).End(xlUp).Row con
Ur2 = sh2.Range("F" & Rows.Count).End(xlUp).Row
Comunque con le varianzioni, Foglio2 tempo = quasi 3 minuti (20.000*4 annate)=80.000 operazioni
Ps. Casoma vedi in alto-alto Funzionante.xlsm Excel (non risponde), non Ti preocupare aspetta senza toccare nulla che finirà.