Excel 2010 Macro per rendere ciclico il copia incolla

[dodofreeski]

Ciao a tutti, ho cercato una risposta al mio problema ma non la ho trovata.
Ho un file in excel molto pesante (170000 righe su xx colonne) e devo fare parecchi SE CONTA.SE e per evitare che si blocchi il programma devo fare poche righe alla volta.
Sono niente pratico dell'utilizzo delle macro e di VBA, ma ho provato a crearne una (grazie all'aiuto del Vs forum) così strutturata: copio la formula dalla prima cella, la incollo in n.X celle successive (esempio 1000) della stessa colonna, successivamente faccio il copia dalla prima cella copiata alla penultima, incollo SOLO VALORI nello stesso range, mi posiziono nell'ultima cella copiata (dove c'è ancora la formula) SALVO (in modo che se si blocca il programma almeno il lavoro fatto è ok), e poi dovrei ripetere il ciclo fino alla XXXXXX riga (es 170000 fine del file)
C'è modo di dire alla macro di ripetere questa successione di comandi fino alla fine

In attesa di vs cortese riscontro vado avanti manualmente con questo lungo lavoro.

[Flash30005]

Ciao Dodofreeski e benvenuto nel forum

Come ho sempre sostenuto le formule se eccessive rendono il foglio pesante e rallenta il calcolo portando anche al crash
Il tuo stratagemma, di inserire formule e copia/incolla solo valori, non risolve il problema perché (questa non l'ho mai capita) nel foglio rimangono "dati fantasma" che rendono il file di dimensioni maggiori anche se le stesse formule alla fine le converti in valori.
(test da fare: file chiuso senza formule annotare le dimensioni, inserimento di molte formule e/o immagini salvataggio, annotazione delle dimensioni, infine cancellazione delle formule e/o immagini, nuovo salvataggio, verifica delle dimensioni che non sono come il file originale).
Opportuno sarebbe creare una macro che sostituisca la formula (effettui il calcolo) e inserisca dove occorre direttamente il valore.
Per fare questo, però, dovremmo avere la struttura dei tuoi dati oppure conoscere la/le formule che usi
Prova a trascrivere qui una formula che usi postando quella che è in alto al foglio (B2, C2 o altro...)

ciao

[dodofreeski]

Ciao adesso capisco il perchè di tutti sti continui crack.
la formula in questione è questa

=SE(CONTA.SE($B$2:$B118;B18)>1;0;1)
=SE(CONTA.SE($C$2:$C118;C118)>1;0;1)
=SE(CONTA.SE($D$2:$D118;D118)>1;0;1)
=SE(CONTA.SE($E$2:$E118;E118)>1;0;1)

dove nelle colonne b c d e ci sono dati che poi nella successiva tabella pivot mi serviranno univoci (in relazione ad altre colonne alle quali ho fatto dei concatena già copiati a valore anche se a quanto pare non serve a niente )
grazie

[Flash30005]

Si ok ma dove sono inserite queste formule?
esempio
=SE(CONTA.SE($B$2:$B118;B18)>1;0;1)
in quale cella si trova?

e così anche le altre

[dodofreeski]

nelle colonne a b c d ci sono i dati. Nelle colonne e f g h ci sono le formule

ti allego file con esempio del db
http://www.filedropper.com/esempiodatabase

[Flash30005]

Una macro che compila con i valori le celle in colonna E, F, G, H potrebbe essere questa

Codice: Seleziona tutto

Sub CompilaF()
UR = Range("A" & Rows.Count).End(xlUp).Row
For RR = 2 To UR
MyCE = Evaluate("=COUNTIF(Foglio1!$B$2:$B" & RR & ",Foglio1!A" & RR & ")")
MyCF = Evaluate("=COUNTIF(Foglio1!$C$2:$C" & RR & ",Foglio1!B" & RR & ")")
MyCG = Evaluate("=COUNTIF(Foglio1!$D$2:$D" & RR & ",Foglio1!C" & RR & ")")
MyCH = Evaluate("=COUNTIF(Foglio1!$E$2:$E" & RR & ",Foglio1!D" & RR & ")")
MioE = 1
MioF = 1
MioG = 1
MioH = 1
If MyCE > 1 Then MioE = 0
If MyCF > 1 Then MioF = 0
If MyCG > 1 Then MioG = 0
If MyCH > 1 Then MioH = 0
Range("E" & RR).Value = MioE
Range("F" & RR).Value = MioF
Range("G" & RR).Value = MioG
Range("H" & RR).Value = MioH
Next RR
End Sub

Prova e fai sapere

ciao

[Flash30005]

Oppure questa...

Codice: Seleziona tutto

Sub CompilaF2()
UR = Range("A" & Rows.Count).End(xlUp).Row
For RR = 2 To UR
MIOE = 1
MIOF = 1
MIOG = 1
MIOH = 1
If Application.WorksheetFunction.CountIf(Range("$B$2:$B" & RR), Range("A" & RR)) > 1 Then MIOE = 0
If Application.WorksheetFunction.CountIf(Range("C$2:$C" & RR), Range("B" & RR)) > 1 Then MIOF = 0
If Application.WorksheetFunction.CountIf(Range("$D$2:$D" & RR), Range("C" & RR)) > 1 Then MIOG = 0
If Application.WorksheetFunction.CountIf(Range("$E$2:$E" & RR), Range("D" & RR)) > 1 Then MIOH = 0
Range("E" & RR).Value = MIOE
Range("F" & RR).Value = MIOF
Range("G" & RR).Value = MIOG
Range("H" & RR).Value = MIOH
Next RR
End Sub

Ciao

[Anthony47]

Vedi le proposte di Flash, sopra.

Mie considerazioni...
Formula in E2: =SE(CONTA.SE($B$2:$B2;A2)>1;0;1)
Formula in B2: =CONCATENA($O2;A2)
In A2: un Valore
Quindi quale e' il significato della formula in E2 (e sottostanti), che conta quante volte in colonna B c' e' lo stesso valore che in A? Si vuole tenere traccia delle volte che la colonna O e' vuota?

Idem dubbio per la formula in F2 [=SE(CONTA.SE($C$2:$C2;B2)>1;0;1)], visto che C2 e' un concatenamento tra lo stesso B2 e altri termini.
Idem per la formula in G2 [=SE(CONTA.SE($D$2:$D2;C2)>1;0;1)], visto che D2 e' un concatenamento tra lo stesso C2 e altri termini.
La formula in H2 [=SE(CONTA.SE($E$2:$E2;D2)>1;0;1)] invece e' strana, perche' conta se D2 (un concatenamento di altre celle) e' uguale a 0 oppure 1 (i possibili valori di colonna E)

Le formule in U:X mi pare abbiano le stesse incongruenza, e (a occhio) idem si puo' dire delle altre formule Conta.Se presenti in AW:AZ e BG:BK.

Quindi io suggerirei di sospendere un attimo il problema della velocita' di calcolo per ragionare su quali sono i dati di partenza e che cosa va complessivamente realizzato.

Infine direi che per poter fare dei test di performance servirebbe almeno un file con 1000-5000 righe.

Ciao a tutti.

[dodofreeski]

ops scusate ma deve esserci stato un problema nella copia della formula.
Le formule giuste sono
=SE(CONTA.SE($A$2:$A2;A2)>1;0;1)
=SE(CONTA.SE($B$2:$B2;B2)>1;0;1)
E COSì VIA
cioè il conta.se deve essere fatto sulla stessa colonna selezionata

devo creare univoci i dati nella colonna A2:A170000 così come nelle altre concatenate B2:B170000 ecc...

scusate per la mia distrazione.

Comunque ho provato a copiare quelle cose per me incomprensibili nel foglio VBA della macro e con tutte e due mi va in crash lo stesso il programma. magari sbaglio io a "copiare" (ovviamente ho sostituito i riferimenti del foglio nella prima soluzione)

[Flash30005]

???
E' sufficiente che sostituisci la colonna corrispondente nella macro che è di facile lettura perché rispecchia la tua formula
quindi se devi controllare nel range colonna A invece di B (nella seconda macro)
questa

Codice: Seleziona tutto

If Application.WorksheetFunction.CountIf(Range("$B$2:$B" & RR), Range("A" & RR)) > 1 Then MIOE = 0

Diventa

Codice: Seleziona tutto

If Application.WorksheetFunction.CountIf(Range("$A$2:$A" & RR), Range("A" & RR)) > 1 Then MIOE = 0

etc etc

ciao

[Flash30005]

Pensandoci bene, secondo me, è sbagliata anche questa ultima formula
perché il controllo della A2 la devi fare su tutta la colonna A e non solo da A2 a A2,
quindi
=SE(CONTA.SE($A$2:$A2;A2)>1;0;1)
dovrebbe essere
=SE(CONTA.SE($A$2:$A$17000;A2)>1;0;1) '<<<< 17000 come esempio ma dovrebbe essere l'ultima riga di A
o sbaglio?

Se così
la riga codice potrà essere

Codice: Seleziona tutto

If Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & RR)) > 1 Then MIOE = 0

ciao

[dodofreeski]

Allora il controllo che devo fare dovrebbe essere questo
A2 in A2:A2 risultato 1
A3 in A2:A3 risultato 1
A4 in A2:A4 risultato 0 (nel caso sia = A3)
A5 in A2:A5 risultato 1
.... ecc...

in modo che se A4=A3 devo avere 0 (così ho la certezza della conta univoca della colonna A)

l' ho provato su 10000 righe circa e funziona (i valori che mi ha dato sono uguali a quelli che avevo inserito io con la formula se conta.se).
Ora lo provo sul Db completo a vedere se si cracka (nel caso lo facesse? ho altre alternative? in quanto per ora ho segnalazione in alto (non risponde)) .

Ultima cosa (spero!) se devo compilare altre colonne con lo stesso criterio che parametro devo cambiare nella macro?
la seconda riga ?? UR = Range("A" & Rows.Count).End(xlUp).Row
e sostituire "A" con la colonna iniziale che mi serve (e ovviamente anche nelle altre righe)?

per ora grazie ancora

[Flash30005]

Se la tua esigenza è quella che confermi con l'ultimo post allora non tener conto della mia ultima osservazione ma solo quella del post ore 16:32

Per le altre formule procedi nella stessa maniera sostituendo solo la lettera della colonna come hai fatto per "A"

Il consiglio che ti do è di copiare i dati (senza vecchie formule o altro ma solo i dati) del range nelle colonne A, B, C, D
non copiare le colonne intere ma da A1 a D170000 (ultima riga)
e incollarle in un nuovo file di Excel
fai la stessa cosa per le altre colonne dati, lasciando chiaramente le colonne E, F, G H vuote
inserisci la macro nel modulo del nuovo file di Excel e la avvii

Per velocizzare l'esecuzione è opportuno inserire 4 righe codice (due a inizio macro e due alla fine) in questa maniera

Codice: Seleziona tutto

Sub CompilaF2()   '<<<<<< Nome inizio macro
Application.ScreenUpdating = False  '<<<<<<<<<< riga da aggiungere
Application.Calculation = xlManual  '<<<<<<<<<< riga da aggiungere

'.... macro
'.... macro

Application.Calculation = xlCalculationAutomatic  '<<<<< riga da aggiungere
Application.ScreenUpdating = True  '<<<<<<<<<<<<<<<<<<< riga da aggiungere
End Sub '<<<<< fine macro

Ciao

[dodofreeski]

. . direi che il nick che hai scelto ti si addice . .
Ora procedo
Grazie ancora a tutti