Formula equivalente ma più veloce.

[Statix]

Ciao Anthony47,
ho allegato un file per il test,
l'altra macro serve per una seconda ricerca,quindi volevo evitare di mettere formule che non uso.
con la prima ricerca,
nella colonna I se è possibile riportare il valore della cella in A e non la formula
la formula è

Codice: Seleziona tutto

=se(conta.se(D6:H6;A2)=1;A6;"")

http://www.filedropper.com/test_9

[Statix]

Ciao Anthony47,
spero che non ti sei dimenticato,
mi occorre almeno le 2 macro con l'aggiunta delle 3 colonne,
per le estrazioni non c'è fretta,
io ci provato a modificare la macro per aggiungere le colonne ma senza risultato,
grazie .

[Flash30005]

Non ho seguito l'intero thread ma se ti occorre una macro che inserisca in colonna I il valore della colonna A quando uno dei valori da D a H sia uguale ad A2 allora puoi usare questa

Codice: Seleziona tutto

Sub ColI()
Worksheets("Spia").Range("I6:I60000").ClearContents
UR = Worksheets("Spia").Range("A" & Rows.Count).End(xlUp).Row
For RR1 = 6 To UR
MyC = Evaluate("COUNTIF(Spia!D" & RR1 & ":H" & RR1 & ",Spia!A2)")
If MyC > 0 Then Worksheets("Spia").Range("I" & RR1).Value = Worksheets("Spia").Range("A" & RR1).Value
Next RR1
End Sub

Ciao

[Statix]

Ciao Flash30005,
la macro che hai postato è ok,
però quello che chiedevo,
a parte di modificare la colonna I come la tua macro,
era quello di modificare la macro di Anthony47, con gli array
aggiungendo altre 3 formule per le colonne,
sotto queste

Codice: Seleziona tutto

 
        Cells(6 + I, "M").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";D2)"
        Cells(6 + I, "N").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";E2)"
        Cells(6 + I, "O").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";F2)"

Codice: Seleziona tutto

colonna K = SE(SOMMA($M6:$O6)>=1;1;"")
colonna Q   =SE.ERRORE(PICCOLO(INDIRETTO("P"& RIF.RIGA()+1 & ":" & "P" & RIF.RIGA()+$I$4);1));"")
colonna R    =$Q6-$I6

grazie.

[Statix]

Sono riuscito ad aggiungere la colonna Q,
adesso provo con le altre 2

Codice: Seleziona tutto

Cells(6 + I, "Q").FormulaLocal = "=Se.Errore(Piccolo(P" & (7 + I) & ":P" & (6 + I + myI4) & ";1);"""")"

[Flash30005]

Non capisco perché usi le macro per inserire delle formule quando puoi mettere direttamente il risultato
Con il risultato il file sarebbe meno "pesante" specialmente, da quanto ho capito, hai moltissime formule.

ciao

[Statix]

Ciao Flash30005,
il programma deve elaborare oltre 10.000.000 di combinazioni,
per snellire il lavoro non ho fatto altro che creare un interfaccia vuota senza formule,
con una macro faccio il preset delle disposizioni delle formule dove effettivamente occorrono ,
in totale ne saranno circa 1000 formule
poi faccio girare dei cicli ,le formule aggiornano di volta in volta i dati,
salvando solo quelli chi mi interessano.
con la macro di Anthony47 ho risparmiato diverse ore di elaborazione,
ho già testato il programma è come velocità è abbastanza gestibile.
considerando la mole di elaborazioni,
naturalmente ne faccio una parte al giorno.
dividendo anche le elaborazioni con amici,una specie di super computer.
sono elaborazioni che si fanno solo una volta all'anno.
PS mi aiuti con le altre 2 formule.
questa mi da un errore ma non ho capito dove sbaglio.

Codice: Seleziona tutto

Cells(6 + I, "K").FormulaLocal = "=Se(Somma(M" & (6 + I) & ":P" & (6 + I) & ";>0"  ;1);"""")"

[ricky53]

Ciao,
hai una parentesi posizionata male ed un ";" di troppo.

Prova in questo modo

Codice: Seleziona tutto

Cells(6 + I, "K").FormulaLocal = "=Se(Somma(M" & (6 + I) & ":P" & (6 + I) & ")>0 ;1;"""")"

[Statix]

Ciao Ricky53,
tutto ok,
mi serve ancora un altra formula forse 2,
formula Q6-I6
Cells(6 + I, "R").FormulaLocal = "=Somma(Q" & (6 + I) & ....

ed eventualmente inserire un'altra formula

=Cells(6 +I,"I").FormulaLocal=........se(conta.se(D6:H6;A2)=1;A6;"") 'in colonna I
Flash30005 ha fatto una macro ,a questo scopo
ma va un po' lenta.

[ricky53]

Ciao,
strano perchè le macro di Flash son sempre valide e veloci.
A te cosa occorre?


ATTENZIONE: una formula (e tu ne hai tante) è sempre più lenta del corrispondente codice scritto con una macro (cosa che Flash già ti aveva scritto)


Comunque per tradurre le tue formule devi dirci cosa vuoi che facciano.

[Flash30005]

Flash30005 ha fatto una macro ,a questo scopo
ma va un po' lenta.

Devi inserire le due righe di codice che bloccano sia il calcolo che l'upgrade screen prima e dopo la macro

ciao

[Statix]

Ciao Flash30005,
avevo già fatto,
ad inserire le 2 istruzioni.

se è possibile modificare la macro di Anthony47,
e inserire la formula che fa la tua macro nell' array.
x ricky53,
la formule da inserire nell'array
sono
=q6-I6 in colonna R

[Statix]

Ok,
ho risolto anche la formula per la colonna R,
rimane solo la modifica alla macro di Anthony47,
per inserire il risultato della formula(no formula) in colonna I

Codice: Seleziona tutto

=se(conta.se(D6:H6;A2)=1;A6;"")

la macro di Flash30005 fa già questo lavoro,
ma vorrei se possibile avere tutto nella macro di Anthony47,
intanto ci provo.

[Anthony47]

Perdonate, ma mi sono perso i vari step della discussione
Ricordo che la colonna I, nella macro che statix usa, viene dumpata in un array; quindi questa colonna va compilata interamente a inizio macro; puo' essere fatto aggiungendo questa istruzione:

Codice: Seleziona tutto

Dim myArr, I As Long, myI4 As Long
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
'
Range("I6:I6000").FormulaLocal = "=SE(CONTA.SE(D6:H6;$A$2)=1;A6;"""")"  '<<< QUESTA


Per inserire in Rnn il calcolo Qnn-Inn usare questa istruzione

Codice: Seleziona tutto

Cells(6 + I, "R") = Cells(6 + I, "Q") - Cells(6 + I, "I")   '   Q6-I6


Mette direttamene il risultato, non la formula.

Non ho capito quali sono le altre domande pendenti.

Ciao

[Statix]

Ciao Anthony47,
ho provato le modifiche la prima mi scrive le formule in tutta la colonna I
mentre dovrebbe scrivere solo il risultato,
mentre la seconda lo già risolta (mi scrive la formula) ed era quello che serviva.

Codice: Seleziona tutto

Cells(6 + I, "R").FormulaLocal = "=se.Errore(Somma(Q" & (6 + I) & "- I" & (6 + I) & ");"""")"

un altra cosa modificare il valore 6000 con Ultima riga,
UR=A4
ho provato nella array . mi ha dato un errore,
mentre nella cancellazione mi ha funzionato

[Anthony47]

Perche' vuoi scrivere i risultati e non le formule? se e' per velocizzare, allora e' piu' veloce scrivere le formule con una unica istruzione che non i risultati in un nuovo loop da aggiungere prima dell' esistente.
Per lavorare con UR, le modifiche sono concentrate sulla parte inziale della macro:

Codice: Seleziona tutto

Sub M6O6006()
Dim myArr, I As Long, myI4 As Long, UR As Long
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
'
UR = Cells(Rows.Count, 1).End(xlUp).Row
Range("I6").Resize(UR - 5, 1).FormulaLocal = "=SE(CONTA.SE(D6:H6;$A$2)=1;A6;"""")"
'
Range("M6").Resize(UR - 5, 6).ClearContents '<<< Resize(UR-5,6) cancella le colonne M:R
myArr = Range("I6").Resize(UR - 5).Value
'
myI4 = Range("I4").Value
For I = 0 To (UR - 6)
'etc etc


[Statix]

Ciao Anthony47,
le modifiche sono ok.
in colonna I
volevo mettere solo i risultati ,per evitare di calcolare le 6000 formule ,ogni volta che faccio un ciclo di ricerca
invece che 300,perderei qualche secondo a scrivere i risultati (operazione una sola volta)
ma credo che poi ne guadagnerei sulla successive ricerche ,
comunque ho risolto con un escamotage
dopo che la macro ha scritto le formule.

Codice: Seleziona tutto

Range("I6:I" & UR).Copy
   
    Range("I6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

[Statix]

Ciao Anthony47,Flash30005,ricky53,

mi servirebbe un ultima macro di Array
la macro deve scrivere le formule nelle colonne
dalla riga 4690 alla Ur (ultima riga)
stò cercando di riuscirci ma ho problemi con la sintassi.
grazie e buona domenica a tutti.

Codice: Seleziona tutto

=SE($L4690>=$I$4;"";SE(CONTA.SE(D4690:H4690;$A$2)=1;$L4690;"")) 'colonna V
=SE(SOMMA(AE4690:AG4690)>=1;1;"")                               'colonna X
=SE($AB4690="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$L4690);D$2)) 'colonna Z
=SE($AB4690="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$L4690);E$2)) 'colonna AA
=SE($AB4690="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$L4690);F$2)) 'colonna AB
=SE(CONTA.NUMERI(AB4690:AD4690)=1;$AB4690;"")  'colonna AD

[Statix]

OK fatto alcune modifiche,
mi servono solo queste 3 formule,
non riesco con la sintassi giusta.

Codice: Seleziona tutto

=SE($L6>=$I$4;"";SE(CONTA.SE(D6:H6;$A$2)=1;$L6;"")) 'Colonna DU
=SE($AB6="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$L6);D$2)) 'Colonna DW
=SE(CONTA.NUMERI(AB6:AD6)=1;$AB6;"")  'Colonna DZ

Codice: Seleziona tutto

 Cells(6 + I, "DU").FormulaLocal = "=Se(L" & (6 + I) & ") >= myI4);"""";Se(Conta.se(D" & (6 + I) & ":H" & (6 + I) & ";A2)=1)&";(L" & (6 + I) & ");"""")"

Codice: Seleziona tutto

Cells(6 + I, "DW").FormulaLocal = "=Se(DU" & (6 + I) = """"";"""";Conta.se(D" & (7 + I) & ":H" & (6 + I + (L" & (6 + I)") &";D2);(L" & (6 + I)&" )"

[ricky53]

Ciao,
ci vuoi far morire !!1

Una la faccio ma ... per le altre ...

Codice: Seleziona tutto

Cells(6 + I, "DU").FormulaLocal = "=SE($L" & 6 + I & ">=$I$4;"""";SE(CONTA.SE(D" & 6 + I & ":H" & 6 + I & " ;$A$2)=1;$L" & 6 + I & ";""""))"

Provala e ...