[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... :D
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]

cos'è il codice utilizzatore? lol :)


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


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 ? :D
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]

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

PS: cos'è una chiamata? XDXDXDXD ;)

Dicesi chiamata... Ma vaaaaa ! :D

[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]

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


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...


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...


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.


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


Adesso hai gli elementi per divertirti :D . 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]

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...



Ti sarai accorto che ciò che chiedevi, e che via via vuoi aggiungere, non è certo elementare...
azz, ho notato ;) thx Marco N°1