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

Prova così.

Edit: ... la soluzione che ti avevo proposto non può andar bene perché ho notato che fai un inserimento simultaneo su più righe e con la FormulaArray non si comporta, con i riferimenti relativi, come on le formule non matriciali.

Answer 3

Ciao Casanmaner,

ti posto il codice completo del foglio InsIsc:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("I2:Q2000").ClearContents

Application.ScreenUpdating = False

Colonna1 = Range("A65536").End(xlUp).Row

Range("I2:I" & Colonna1).Value = Range("B2:B" & Colonna1).Value

Range("J2:J" & Colonna1).FormulaLocal = "=SE($A2>0;CERCA.VERT($e2;Categorie!$B:$C;2;0);"""")"

Range("K2:K" & Colonna1).FormulaLocal = "=SE(E($A2>0;$B2>0);CERCA.VERT($e2; Categorie!$B:$C;2;0);"""")"

Range("L2:L" & Colonna1).FormulaLocal = "=SE(E($A2>0;$B2>0);CERCA.VERT($e2;Categorie!$B:$D;3;0);"""")"

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);"""")"

Range("N2:N" & Colonna1).FormulaLocal = "=SE(A2>0;SE(VAL.ERRORE(CERCA.VERT($C2;DbaseTesserati!$C:$M;7;0));0;CERCA.VERT($C2;DbaseTesserati!$C:$M;7;0));"""")"

Range("P2:P" & Colonna1).FormulaLocal = "=SE($M2<"""";CERCA.VERT($E2;Categorie!$AI:$AL;4;0);"""")"

'Range("O2:O" & Colonna1).FormulaLocal = "=SE(P2=1;Dati!$C$25*2;SE(P2=2;Dati!$C$25;SE(P2=3;Dati!$C$25*2;SE(P2=4;Dati!$C$25;SE(P2=5;Dati!$C$25;"""")))))" 'pt el/un e amatori

Range("O2:O" & Colonna1).FormulaLocal = "=SE(P2=5;Dati!$C$25;SE(P2=4;Dati!$C$25;SE(P2=3;Dati!$C$25;SE(P2=2;Dati!$C$25;SE(P2=1;Dati!$C$25;"""")))))"    'pt a tutti

'Range("O2:O" & Colonna1).FormulaLocal = "=SE(Q2=0;Dati!$C$25;SE(Q2=1;Dati!$C$25*2;))"               'pt per elite e amatori

Range("Q2:Q" & Colonna1).FormulaLocal = "=SE($M2<"""";CERCA.VERT($E2;Categorie!$AI:$AN;6;0);"""")"  'se elite o amatori allora pt*2

'With Range("A2:Z1000")

'.Value = .Value

'End With

'[A1] = "n dors"

    '[b1] = "P"

    '[c1] = "Cognome e Nome"

    '[d1] = "Licenza"

    '[e1] = "Cat"

    '[F1] = "Cod UCI"

    '[g1] = "Societa"

    '[h1] = "Cod Soc"

    '[I1] = "Sesso"

    [J1] = "Cod Isc"

    [K1] = "Cod Par"

    [L1] = "num part"

    [M1] = "cod premi"

    [N1] = "pt top class"

    [O1] = "pt"

    [P1] = [Dati!C24].Value

        Range("AA2:An10000").ClearContents

End Sub

Fa parte di un piccolo programma di gestione di gare ciclistiche che ho elaborato in modo del tutto "amatoriale" e che grazie ai vostri suggerimenti e discussioni continuo a modificare.

Se ti può interessare ti posso inviare il programma completo che per ovvii motivi di inserimento di dati sensibili ti posso far avere personalmente. Il mio indirizzo mail è: ******@libero.it

Answer 4

Ancora una cosa.

Sempre se la formula la inserisci tramite VBA e tu non volessi inserire le condizioni nel foglio (con relativo nome dell'intervallo, ma gestire tutto tramite codice potresti sempre inserire la formula in modalità array e creare una stringa di testo con tutte le condizioni come questo esempio:

Sub Test()

Dim sCondizioniVietate  As String

sCondizioniVietate = "{""CSI"",""ACSI"",""AICS"",""ASC""}"

    ActiveCell.FormulaArray = "=IF(AND(R[166]C1>0,R[166]C2>0,R[166]C[1]<>" & sCondizioniVietate & _

                              "),VLOOKUP(R[166]C5,Categorie!C2:C7,4,0),"""")"

End Sub

Nota che dove ho inserito la formula (ActiveCell) era la G2 e la formula nella cella risulta quindi essere informa matriciale:

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

Answer 5

Si, mi sembra un'ottima soluzione.

Ci provo e ti dico.

Grazie da Mauro