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

Ciao Norman, ti posto il codice completo in cui ho l'errone di run-time 1040:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Application

     .ScreenUpdating = False

     .Calculation = xlCalculationManual

     '.EnableEvents = False

   End With

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("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("M2:M" & Colonna1).FormulaLocal = "=SE(E($A2>0;VAL.ERRORE(CONFRONTA(H2;TabellaVietati[#Data];0)));CERCA.VERT($E2;TabellaRicerca;4;0);"""")"

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

Range("Q2:Q" & Colonna1).FormulaLocal = "=SE($M2<"""";CERCA.VERT($E2;Categorie!$AI:$AN;6;0);"""")"

    [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

    With Application

     .ScreenUpdating = True

     .Calculation = xlCalculationAutomatic

     '.EnableEvents = True

   End With

End Sub

Non riesco a capire dove sbaglio e soprattutto TabellaVietati[#Data]. Che significato ha #Data ?

Ti ringrazio. Ciao da Mauro

Answer 3

Il codice al momento mi dà il classico errore di run-time 1004.

Probabilmente l'errore è dato dal riferimento alla Tabella che forse non è gradita.

Ti consiglierei questo tipo di approcio.

Creare un foglio apposito (nominato ad es. Appoggio) dove, per semplicità, senza intestazioni o altro in colonna  A inserire in righe contigue il testo dei "CodiciVietati". Foglio che volendo potrai tenere nascosto e scoprire solo all'occorrenza quando avrai necessità di aggiungere codici "vietati".

Poi inserire nel modulo VBA del foglio in questione questo codice:

Private Sub Worksheet_Deactivate()

  Me.Range("A1").CurrentRegion.Name = "CondizioniVietate"

End Sub

In questo modo ogni volta che "accoderai" un codice vietato nel momento in cui selezionerai un qualsiasi altro foglio, e di conseguenza verrà deselezionato il foglio "Appoggio", il nome dell'intervallo con le condizioni vietate si aggiornerà automaticamente.

Poi nel tuo codice di inserimento formule potrai inserire la formula di Norman, che non è matriciale, ma che faccia riferimento non alla "Tabella" ma al nome dell'intervallo delle celle.

Non essendo matriciale potrai, per coerenza con le altre formule, utilizzare FormulaLocal con inserimento dell'intero intervallo.

In pratica potrai scrivere così:

  Range("M2:M" & Colonna1).FormulaLocal = "=SE(E($A2>0;$B2>0;VAL.ERRORE(CONFRONTA(H2;CondizioniVietate;0)));" & _

                                          "CERCA.VERT($E2;Categorie!$B:$G;4;0);"""")"

Prova a fare queste modifiche.

Qui un file esempio molto banale

test

Nota che le formule vengono inserite nel foglio Dati tramite l'evento Worksheet_SelectionChange che richiama una routine che, come nel tuo programma, conta l'ultima riga e inserisce solo quella formula.

Poi ci sarebbe da discutere sull'opportunità di utilzzare l'evento Worksheet_SelectionChange per inserire le formule e soprattutto per inserire nuovamente formule che dovrebbero essere già state inserite.

Ma questa è altra questione e probabilmente tu avrai i tuoi motivi per utilizzare questo approcio.

Answer 4

Eccomi ritornato, ho provato i suggerimenti di casanamer e con l'inserimento delle due dichiarazioni funziona velocemente come prima.

Il mio scopo era quello di verificare che non ci fossero quelle condizioni vietate che ho inserito e che posso aggiungerne altre inserendole nella formula.

Mi sembra ottima anche la proposta di Norman di inserire un elenco o tabella a parte con possibilità di aggiornamento.

Il codice al momento mi dà il classico errore di run-time 1004.

Riprovo questa sera e vi dico

Answer 5

Dovete scusarmi se non vi ho risposto subito ma sono appena rientrato da una due giorni ciclistica.

Domani mi metto a smanettare e poi vi dico, fatto salvo che i miei nipotini (4 e 10 anni quindi nonno a tempo pieno) mi lascino un poco di spazio per farmi lavorare e non solo giocare.

Vi ringrazio dell'attenzione che mi prestate e da cui se ne traggono utilissime indicazioni.

Il programmino che ho messo in piedi ( e che pezzo x pezzo continuo ad aggiornare) è solo frutto delle vostre indicazioni delle discussioni che ormai seguo da anni.

Ciao a tutti da Mauro