[Excel]cerca.vert

[swr]

ciao, come si effettua un cerca.vert su tutti i fogli?

tipo =cerca.vert("HW";Foglio1!Foglio2!Foglio3!;3;FALSO)
non funziona nemmeno se inserisco il nome della cartella di lavoro =cerca.vert("HW";Cartella di lavoro!;3;FALSO)...boh suggerimenti?

[MarcoGG]

Come singola Funzione non si può fare, perchè ovviamente ritornerebbe un valore per ogni Foglio. Se ad esempio vuoi una Somma tra tutti i CERCA.VERT sui vari Fogli, puoi costruirti una Funzione che somma ogni singolo CERCA.VERT per ogni Foglio.
Se invece, indipendentemente dal numero dei Fogli, vuoi fare un ciclo e ricevere un array di risultati, la strada è VBA...

[swr]

ciao Marco ...devo cominciare a studiare un pò di VBA perchè è un ottimo strumento quando ci si trova con formule troppo complesse, sul quesito dell'altro giorno infatti non sono riuscito ad applicarlo XD

tornando all'oggetto...
purtroppo i fogli risulteranno alla fine circa 150 quindi inserli nella funzione mi pare proibitivo, la ricerca dovrebbe risultare in questo modo....

cella Q5 con il valore "Pluto" in Foglio1 da cercare in Foglio2, Foglio3, Foglio4 etc...lo trova ad es. nel Foglio75 nella cella AG35 e dovrebbe riportare l'intervallo AG36:AG40 in Q6:Q10 del Foglio1

se non risultasse impegnativo un'istruzione VBA con i valori come in esempio come dovrebbe essere?

[MarcoGG]

Però. 150 Fogli. Ti stai intrippando in qualcosa di grandicello...
A sto punto direi che il CERCA.VERT ce lo siamo giocato. Ok, appena ho tempo ci provo...

[swr]

asd XD è vero perchè per ogni foglio ci sono riferimenti a dati esterni che se collocassi assieme diventerebbe troppo incasinato cmq thx gentilissimo^^

[MarcoGG]

OK, prova questa soluzione :

1. In un modulo :

Public Function UltimaCellaUtile(nomeFoglio As String) As String

    Dim UC As Integer
    Dim UR As Long
    If WorksheetFunction.CountA(Worksheets(nomeFoglio).Cells) > 0 Then
        UC = Worksheets(nomeFoglio).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        UR = Worksheets(nomeFoglio).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        UltimaCellaUtile = Cells(UR, UC).Address
    Else
        UltimaCellaUtile = Cells(1, 1).Address
    End If
 
End Function

Public Sub Esegui()

    Dim cellaRicerca As String
    cellaRicerca = "Q5"
    Dim cellaIncolla As String
    cellaIncolla = Range(cellaRicerca).Offset(1, 0).Address ' Address Q6
    MsgBox cellaIncolla

    Dim valRicerca As Variant
    valRicerca = Range(cellaRicerca).Text
    Dim F As Worksheet
    Dim R As Range
    Dim cellaLimite As String
    For Each F In ThisWorkbook.Worksheets
        If F.Name <> "Foglio1" Then
            cellaLimite = UltimaCellaUtile(F.Name)
            For Each R In F.Range("A1:" & Range(cellaLimite).Address)
                'Trova la prima occorrenza di valRicerca / Esegui / Esci dal ciclo
                If R.Text = valRicerca Then
                    MsgBox R.Address
                    F.Range(R.Offset(1, 0).Address, R.Offset(5, 0).Address).Copy
                    Worksheets("Foglio1").Range(cellaIncolla).Select
                    ActiveSheet.Paste
                    GoTo FINE
                End If
            Next R
        End If
    Next F
    
FINE:

End Sub

2. Codice utilizzatore ( ad esempio Button su Foglio1... ) :

Esegui

Direi che c'è tutto, chiaramente andrà modificato qualora i cicli debbano continuare dopo aver trovato la prima occorrenza ecc..., ma il concetto di base dovrebbe essere questo.

[swr]

cos'è il codice utilizzatore? lol

cmq ho provato la macro, praticamente cerca il valore di Q5(Foglio1), lo trova in AG35(Foglio75) copia il range AG36:AG40(Foglio 75) e lo incolla nel range Q6:Q10(Foglio1)...il problema è che all'interno del range trovato ci sono delle formule, quindi ovviamente incolla pure quelle ed esce #RIF!

[MarcoGG]

Originariamente inviato da swr

cos'è il codice utilizzatore? lol

Un modo un po' forbito per dire che quella chiamata ad "Esegui" fa "partire tutto" ( cos'è una chiamata ? )...

Originariamente inviato da swr

cmq ho provato la macro, praticamente cerca il valore di Q5(Foglio1), lo trova in AG35(Foglio75) copia il range AG36:AG40(Foglio 75) e lo incolla nel range Q6:Q10(Foglio1)...il problema è che all'interno del range trovato ci sono delle formule, quindi ovviamente incolla pure quelle ed esce #RIF!

Beh, allora direi che va tutto bene, no ?
A questo punto piglia la vecchia Sub Esegui(), e sostituiscila con questa :

Public Sub Esegui()

    Dim cellaRicerca As String
    cellaRicerca = "Q5"
    Dim cellaIncolla As String
    cellaIncolla = Range(cellaRicerca).Offset(1, 0).Address ' Address Q6
    MsgBox cellaIncolla

    Dim valRicerca As Variant
    valRicerca = Range(cellaRicerca).Text
    Dim F As Worksheet
    Dim R As Range
    Dim cellaLimite As String
    For Each F In ThisWorkbook.Worksheets
        If F.Name <> "Foglio1" Then
            cellaLimite = UltimaCellaUtile(F.Name)
            For Each R In F.Range("A1:" & Range(cellaLimite).Address)
                'Trova la prima occorrenza di valRicerca / Esegui / Esci dal ciclo
                If R.Text = valRicerca Then
                    MsgBox R.Address
                    F.Range(R.Offset(1, 0).Address, R.Offset(5, 0).Address).Copy
                    Worksheets("Foglio1").Range(cellaIncolla).PasteSpecial Paste:=xlValues, Operation:=xlPasteSpecialOperationNone
                    GoTo FINE
                End If
            Next R
        End If
    Next F
    
FINE:

End Sub

[swr]

ciao grande!^^ torno solo adesso, domani provo l'istruzione e ti dico...

PS: cos'è una chiamata? XDXDXDXD

[MarcoGG]

Originariamente inviato da swr

ciao grande!^^ torno solo adesso, domani provo l'istruzione e ti dico...

PS: cos'è una chiamata? XDXDXDXD

Dicesi chiamata... Ma vaaaaa !

[swr]

provato e funziona perfettamente, mittico! comincio a capire anche qualche istruzione tipo gli offset per i range. Alcune domande di dettaglio e contorno XD...

1) quando la macro completa il ciclo, visualizza 2 "msgbox" con le celle Q6 e AG35...è possibile eliminarli?

2) se Q6(valore da cercare) è vuota crea il copia/incolla da A1 con altri dati, vorrei invece che rimanesse vuota anche facendo partire la macro

3) precedentemente hai fatto riferimento al fatto che si poteva modificare l'istruzione nel caso in cui i cicli devono continuare la ricerca anche dopo aver trovato la prima occorrenza; ecco, volendolo adattare ad altro....tipo: cerca Q5 lo trova nel Foglio75 copia/incolla range in Q6...cerca Q5 lo trova nel Foglio90 copia/incolla range in Q7...cerca Q5 lo trova nel Foglio103 copia/incolla range in Q8 etc...

4) che non è correlata alla macro...avvolte aggiornando alcune query viene inserita una colonna extra che ovviamente va a "sballare" i cerca.vert...sempre con l'ausilio di VBA, si può eliminare in automatico questa colonna extra se viene creata una volta aggiornata la query?

direi che è tutto lol XD se è troppo NP sei già stato un grande

[MarcoGG]

Originariamente inviato da swr

1) quando la macro completa il ciclo, visualizza 2 "msgbox" con le celle Q6 e AG35...è possibile eliminarli?

Certo. le avevo lasciate solo a scopo di "debug". Elimina le due linee di codice :

MsgBox cellaIncolla

MsgBox R.Address

Originariamente inviato da swr

2) se Q6(valore da cercare) è vuota crea il copia/incolla da A1 con altri dati, vorrei invece che rimanesse vuota anche facendo partire la macro

Chiaramente se vuoi usare la Sub Esegui() in un ciclo dovrai poi gestire le varie eccezioni che prevedi si presentino. Nel caso della cella origine vuota, condizionare con un If, in modo che non entri nel ciclo successivo...

Originariamente inviato da swr

3) precedentemente hai fatto riferimento al fatto che si poteva modificare l'istruzione nel caso in cui i cicli devono continuare la ricerca anche dopo aver trovato la prima occorrenza; ecco, volendolo adattare ad altro....tipo: cerca Q5 lo trova nel Foglio75 copia/incolla range in Q6...cerca Q5 lo trova nel Foglio90 copia/incolla range in Q7...cerca Q5 lo trova nel Foglio103 copia/incolla range in Q8 etc...

Certo. Dovrai anzitutto eliminare l'istruzione "GoTo FINE" e sostituirla con un "Exit For", in tal caso la ricerca della prima occorrenza su un determinato Foglio non produrrà più l'uscita dal ciclo For esterno ( quello sui Fogli ), ma il passaggio al Foglio successivo.
Inoltre dovrai inserire un contatore per generare, ad ogni occorrenza trovata, il nuovo Range in cui incollare...

Originariamente inviato da swr

4) che non è correlata alla macro...avvolte aggiornando alcune query viene inserita una colonna extra che ovviamente va a "sballare" i cerca.vert...sempre con l'ausilio di VBA, si può eliminare in automatico questa colonna extra se viene creata una volta aggiornata la query?

Questo non mi è chiaro. E' un po' difficile spiegare "a distanza", man mano che le cose si fanno più complicate. In ogni caso l'eliminazione di una colonna non desiderata è semplicissima :

Columns("F:F").Delete Shift:=xlToLeft

> elimina tutta la colonna F ed esegue lo Shift di tutto il contenuto del Foglio
che si trova a destra della colonna eliminata, verso sinistra.

Originariamente inviato da swr

direi che è tutto lol XD se è troppo NP sei già stato un grande

Adesso hai gli elementi per divertirti . Ti sarai accorto che ciò che chiedevi, e che via via vuoi aggiungere, non è certo elementare...

Un ultimo consiglio che ti do, soprattutto avendo a che fare con VBA, che non è certo il linguaggio più "riutilizzabile" e "rimodificabile" del mondo, è anzitutto di mettere in chiaro da SUBITO cosa vuoi ottenere ( fai un'analisi completa del progetto prima di scrivere codice ), e cerca per quanto ti è possibile di parametrizzare tutto quello che fai.

[swr]

Originariamente inviato da MarcoGG

Inoltre dovrai inserire un contatore per generare, ad ogni occorrenza trovata, il nuovo Range in cui incollare...

al momento non saprei proprio come fare, più avanti forse...

Originariamente inviato da MarcoGG

Ti sarai accorto che ciò che chiedevi, e che via via vuoi aggiungere, non è certo elementare...

azz, ho notato thx Marco N°1