Formula con array?

Question

Avrei un problema relativo aduna formula inserita in un evento Worksheet_SelectionChange che mi cerca vero/falso in una lista di dati inseriti in una colonna di altro foglio:

=SE(E($A168>0;$B168>0;H168<>"CSI";H168<>"ACSI";H168<>"AICS";H168<>"ASC";H168<>"ACS";H168<>"ARCI";H168<>"ASI";H168<>"UNLAC";H168<>"FRA");CERCA.VERT($E168;Categorie!$B:$G;4;0);"")

E' possibile evitare di mettere tutti questi dati da cercare magari con una possibile array?

Vi ringrazio

Answer 1

Per ottenere lo stesso risultato, a meno di altre soluzioni che non conosco, occorre effettuare un ciclo (la qual cosa potrebbe rallentare un po' la procedura). Eventualmente oltre a disabilitare lo screenupdating potresti impostare il Calculation in manuale e poi reimpostarlo in automatico alla fine.

Dopo Private Sub Worksheet_SelectionChange(ByVal Target As Range) inserisci queste due dichiarazioni:

  Const strCond As String = "{""CSI"",""ACSI"",""AICS"",""ASC""}" '<--- da implementare con le altre condizioni

  Dim rng As Range

al posto di Range("M2:M" & Colonna1).FormulaLocal = "=SE(E($A2>0;$B2>0;H2<>""CSI"";H2<>""ACSI"";H2<>""AICS"";H2<>""ASC"";H2<>""ACS"";H2<>""ARCI"";H2<>""ASI"";H2<>""UNLAC"";H2<>""FRA"");CERCA.VERT($e2;Categorie!$B:$G;4;0);"""")"

Sostituisci con:

  For Each rng In Range("M2:M" & Colonna1)

    rng.FormulaArray = _

    "=IF(AND(RC1>0,RC2>0,RC[-5]<>" & _

    strCond & "),VLOOKUP(RC5,Categorie!C2:C7,4,0),"""")"

  Next rng

queste sono le formule che ottengo:

M2--> {=SE(E($A2>0;$B2>0;H2<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E2;Categorie!$B:$G;4;0);"")}

M3--> {=SE(E($A3>0;$B3>0;H3<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E3;Categorie!$B:$G;4;0);"")}

M4--> {=SE(E($A4>0;$B4>0;H4<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E4;Categorie!$B:$G;4;0);"")}

....

Fai una prova.

ciao

Answer 2

Se la formula la inserisci tramite l'evento change quindi dovresti utilizzare "FormulaArray" per inserirla tramite VBA in luogo del FormulaR1C1 (o delle altre modalità di inserimento della formula eventualmente utilizzata).

Answer 3

Potresti pensare a creare un elenco delle condizioni "vietate" (nel foglio stesso o magari in un foglio di "appoggio") e nominare l'intervallo (es. CondizioniVietate). In questo intervallo inserire il testo delle varie condizioni vietate.

E poi scrivere la formula così:

=SE(E($A168>0;$B168>0;H168<>CondizioniVietate);CERCA.VERT($E168;Categorie!$B:$G;4;0);"")

da inserire in modalità matriciale CTRL+MAIUSC+INVIO

Poi se in futuro tu dovessi aggiungere altre condizioni "vietate" ti basterebbe inserire le nuove condizioni per cui H168 deve essere diverso da uno di quei valori ed estendere l'intervallo a cui fa riferimento il nome.

Prova a vedere se funziona :-)

Answer 4

Buongiorno Norman,

effettivamente hai ragione nel dire che non è proprio necessaria una soluzione VBA dato che la formula riesce a rispondere alle mie esigenze. Mi costa molto meno eventualmente aggiungere altre condizioni che al momento non mi sono necessarie.

Ti ringrazio per l'attenzione che mi hai prestato e vi seguo sempre con grande attenzione.

Ciao da Mauro

Answer 5

Ciao Mauro,

Avrei un problema relativo aduna formula inserita in un evento Worksheet_SelectionChange che mi cerca vero/falso in una lista di dati inseriti in una colonna di altro foglio:

=SE(E($A168>0;$B168>0;H168<>"CSI";H168<>"ACSI";H168<>"AICS";H168<>"ASC";H168<>"ACS";H168<>"ARCI";H168<>"ASI";H168<>"UNLAC";H168<>"FRA");CERCA.VERT($E168;Categorie!$B:$G;4;0);"")

E' possibile evitare di mettere tutti questi dati da cercare magari con una possibile array?

Non vedo il nesso tra la tua formula ed una routine Worksheet_SelectionChange, ma non hai postato nè il tuo codice nè il tuo file. Detto questo, se si tratti di una sola formula, mi pare poco probabile che questa sia meno efficiente che una soluzione VBA e, avendo già ottenuta una formula adatta, non vedo il motivo per cercare un'alternativa soluzione.

Pertanto, ti chiederei di gentilmente caricare il tuo file, depurato di dati sensibili, su un servizio filesharing, del tipo Microsoft OneDrive o DropBox, e di postare un link al file in una risposta qui. Inoltre, sarebbe utile se spiegassi chiaramente. e più dettagliamente, il problema che vuoi superare.

===

Regards,

Norman