[vba in excel] Metodo Cells.Find Macro

[mauimaui4]

Ho il seguente problema:

A...B..........................C.....................D...........E

hit..HIBERNACULUM.....d:\colonna........0322......Componimento
hit..CARPET CRAWL.....d:\colonna........05c2......Intermezzo
hit..SEBASTIAN...........d:\colonna........3d2f......Anteprima
hot..PAVANE...............d:\segmento.....2aba......Anteprima
hot..CLOUDS..............d:\sezione.........22cc......Nota
mag..STAIRWAY.........d:\sezione.........3345......Selezione

( I puntini sarebbero esclusi li ho messi per dare una formattazione al testo indicato nelle colonne) Ho queste 5 colonne su un file excel ora dovrei creare una macro find testuale attraverso uno userform che permetta di trovare una stringa o parte di essa la cui ricerca deve essere circoscritta solo alle colonne A B ed E ; QUESTO ESCLUDENDO LA RICERCA DEL TESTO PER LE COLONNE C e D.
In poche parole avrei bisogno di creare un solo range che contempli tutte le celle della colonna A della B e della E.

Qui posto il mio codice che contempla la semplice ricerca all'interno del foglio EXCEL senza alcuna limitazione sulle colonne.
Macro da inserire nel modulo:
------------------------------
Sub Macro1()


Find_Text.Show
End Sub
------------------------------
codice della userform: costituita da una semplice TextBox1 dove scrivere il testo e un command_button che permette di esegire la ricerca
------------------------------
Private Sub CommandButton1_Click()
Dim File_To_Play As String
File_To_Play = TextBox1
Set RangeObject = Cells.Find(What:=File_To_Play, After:=ActiveCell, LookAt:=xlPart)
If RangeObject Is Nothing Then

Else: RangeObject.Select
End If

End Sub
------------------------------
Sono stato costretto ad utilizzare il set RangeObject senno' non potevo dirgli che se non trovava niente non doveva dare errore nell'esecuzione del codice.

2) Se non fosse troppo complesso mi piacerebbe, in seconda battuta, Capire come aggiungere nello userform un text_box ove apporvi un numero es. 23 {magari attivabile con un pulsante di opzione o una casella di controllo (che non so usare)} che indicherebbe di iniziare la ricerca saltando le 23 righe al di sotto di dove si trova al momento la cella attiva e quindi a partire magari dalle 23 righe successive rispetto a dove il cursore si trova posizionato; ed in caso di esito negativo della ricerca memorizzare e rimettere il cursore nella posizione in cui si trovava prima della ricerca.

Ringrazio cordialmente a chi mi vorra' rispondere.
Ciao Da Maui

[MarcoGG]

Originariamente inviato da mauimaui4

Ho queste 5 colonne su un file excel ora dovrei creare una macro find testuale attraverso uno userform che permetta di trovare una stringa o parte di essa la cui ricerca deve essere circoscritta solo alle colonne A B ed E ; QUESTO ESCLUDENDO LA RICERCA DEL TESTO PER LE COLONNE C e D.

Non è necessario usare per forza Cells.Find e RangeObject. Puoi risolvere semplicemente con un ciclo For Each sul Range, e la Funzione InStr().
Il codice essenziale per ciò che hai chiesto è qualcosa di simile a questo :

Private Sub cmd_search_Click()

    If TextBox1.Text = "" Then Exit Sub

    Dim strRicerca As String
    strRicerca = TextBox1.Text
    Dim rangeRicerca As String
    rangeRicerca = "A:A,B:B,E:E"
    Dim R As Range
    For Each R In Range(rangeRicerca)
        If InStr(1, R.Text, strRicerca, vbBinaryCompare) > 0 Then
            R.Select
            '...
            '...
            Exit For
        End If
    Next R

End Sub

[mauimaui4]

Grazie per il tuo pregevole aiuto,
Ho provato e mi sono permesso di sostituire a occhio la stringa:
-
- If InStr(1, R.Text, strRicerca, vbBinaryCompare) > 0 Then
- If InStr(1, R.Text, strRicerca, vbTextCompare) > 0 Then
Binary con text mi serviva che il valore cercato fosse Case_Insensitive
[ Io vengo da UNIX Shell Bash il (VBA mi fa davvero impazzire con le sue paranoie di sintassi) ]

Comunque non conoscendo bene le funzioni che hai utilizzato tipo:
instr(1,
:::::
Next R

La routine non fa il find ---- Next


Non so come procedere con ricerche successive.
Cioe' lui mi trova la prima occorrenza del testo ma poi se quel testo si ripete ancora in una delle 3 colonne interessate lui non lo cerca piu' fermandosi solo alla prima occorrenza trovata.
Il file contiene circa 10000 righe la probabilita' che un testo si ripeta e' molto alta per questo vorrei trovare passo passo, via via, tutte le occorrenze successive.

Grazie

[MarcoGG]

Originariamente inviato da mauimaui4

La routine non fa il find ---- Next


Non so come procedere con ricerche successive.
Cioe' lui mi trova la prima occorrenza del testo ma poi se quel testo si ripete ancora in una delle 3 colonne interessate lui non lo cerca piu' fermandosi solo alla prima occorrenza trovata.
Il file contiene circa 10000 righe la probabilita' che un testo si ripeta e' molto alta per questo vorrei trovare passo passo, via via, tutte le occorrenze successive.

Grazie

Certo. L'esempio funziona così. Se vuoi tutte le occorrenze in un colpo solo basta che elimini l'istruzione "Exit For". Ovvio che devi inserire nel ciclo le istruzioni necessarie a trattare le occorrenze trovate e/o registrarle da qualche parte...
Se invece ad ogni Click vuoi che trovi solo l'occorrenza seguente e si fermi, dovrai tenere traccia dell'Address di cella dell'ultima occorrenza trovata e ripartire da lì ad ogni ricerca successiva...

[mauimaui4]

La seguente istruzione che segue da me in precedenza utilizzata funzionava facendo proprio il find next passo passo Ma non riuscivo a modularla su colonne specifiche.

Set RangeObject = Cells.Find(What:=File_To_Play, After:=ActiveCell, LookAt:=xlPart)

Ad Ogni modo, partendo dal codice che tu stesso mi hai suggerito penso sia sufficiente permettere a questa routine di essere riciclata nuovamente ad ogni pressione del click button della userform mantenuta aperta fiinche' non la si chiude col pulsante di chiusura. Proprio come avviene per il comando trova di excel.


Non c'e' verso di riciclare sta istruzione a ogni click di esecuzione dellla macro sulla userform ?

If InStr(1, R.Text, strRicerca, vbTextCompare) > 0 Then
R.Select

Oppure
che vuol dire dovrai tenere traccia dell'Address di cella dell'ultima occorrenza trovata e ripartire da lì ad ogni ricerca successiva, come faccio a tracciare l'address e a ridarglielo in pasto all'istruzione mantenendo aperta la userform?



Grazie

[MarcoGG]

Se vuoi restare sul metodo .Find, a questo punto, molto meglio lanciarlo direttamente dal range di ricerca ( che andrà opportunamente definito ), anzichè dall'insieme generico Cells. Personalmente risolverei così :

UserForm ricerca :

1. Dichiarazione del rangeRicerca a livello di Form :

Private rangeRicerca As Range

2. Definizione del rangeRicerca ( ad ogni avvio della Form ) :

Private Sub UserForm_Initialize()

    Dim fineRange As Long
    fineRange = UltimaRigaUtile("Foglio1")
    Dim rangeColonne As Range
    Set rangeColonne = Range("A:A,B:B,E:E")
    Dim rangeRighe As Range
    Set rangeRighe = Range("A1:E" & fineRange)
    Set rangeRicerca = Application.Intersect(rangeColonne, rangeRighe)
    
End Sub

Private Function UltimaRigaUtile(nomeFoglio As String) As Long

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

End Function

3. Pulsante CERCA della Form :

Private Sub cmd_search_Click()

    If TextBox1.Text = "" Then Exit Sub
    Dim strRicerca As String
    strRicerca = TextBox1.Text
              
    Dim rangeTrovato As Range
    Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)
    If Not rangeTrovato Is Nothing Then rangeTrovato.Select

End Sub

Da notare che lo stesso oggetto Range VBA espone il metodo .Find :

Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)

E il gioco è fatto !

[mauimaui4]

Che devo dirti...... Bene Funziona.
Anzi ti diro' di piu' ho dovuto fare un if sul foglio attivo perche' se il foglio attivo cambia anche il mio range di ricerca deve cambiere, e funziona tutto bene.

Anche se non ho capito perche' funziona.
Come non ho capito se la quantita' di codice scritto avrebbe potuto essere piu' essenzianle.

Solo per curiosita'

1) non ho capito perche' si scrive una stringa a inizio form:
>Private rangeRicerca As Range

e muore cosi' punto e basta.

2) Non ho capito perche' ad esempio se il foglio attivo dovesse essere il foglio2 che ha ad esempio un range di questo tipo:
Set rangeColonne = range("A:A,B:B,F:F")
quando te scrivi la stringa del tipo:
> Set rangeRighe = Range("A1:E" & fineRange)
Io in questo caso la dovrei modificare in questa:
Set rangeRighe = range("A1:F" & fineRange)
Cioe' ponendo come intervallo la prima Riga e l'ultima chiamiamola colonna di ricerca che in questo caso e' F

In realta' pero' anche se metto L funziona lo stesso
era proprio necessario scomodare sto set rangeRighe ????

Grazie

(Rispondimi solo se hai tempo.)
Ciao

[MarcoGG]

Originariamente inviato da mauimaui4

Che devo dirti...... Bene Funziona.

Eh eh, certo che funziona.

Originariamente inviato da mauimaui4

1) non ho capito perche' si scrive una stringa a inizio form:
>Private rangeRicerca As Range

e muore cosi' punto e basta.

No no, non "muore così punto e basta". Fai attenzione : è stato dichiarato a livello di UserForm per una questione di pulizia del codice e di funzionalità :

- ne ho bisogno nella UserForm_Initialize(), quando viene definito, una volta sola, all'apertura della Form stessa :

Set rangeRicerca = Application.Intersect(rangeColonne, rangeRighe)

- ne ho bisogno inoltre nella cmd_search_Click() :

Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)

Se non lo avessi messo fuori da queste routine avrei dovuto ridefinirlo e ricalcolarlo ogni volta ad ogni Click sul pulsante cmd_search ! Soluzione che sarebbe stata inutilmente onerosa e non elegante. Prova pure in presenza di qualche decina di migliaia di righe...

Originariamente inviato da mauimaui4

:
2) Non ho capito perche' ad esempio se il foglio attivo dovesse essere il foglio2 che ha ad esempio un range di questo tipo:
Set rangeColonne = range("A:A,B:B,F:F")
quando te scrivi la stringa del tipo:
> Set rangeRighe = Range("A1:E" & fineRange)
Io in questo caso la dovrei modificare in questa:
Set rangeRighe = range("A1:F" & fineRange)
Cioe' ponendo come intervallo la prima Riga e l'ultima chiamiamola colonna di ricerca che in questo caso e' F

In realta' pero' anche se metto L funziona lo stesso
era proprio necessario scomodare sto set rangeRighe ????

Certo che funziona lo stesso.

- rangeColonne contiene tutte le colonne di ricerca, senza però sapere dove realmente finiscono i dati ! Potrei avere 3 colonne, con i dati che finiscono alla riga 100, e se lasciassi fare a lui mi andrebbe in search su tutte le oltre 65000 colonne ( Excel 2003 ), allucinante se pensiamo ad Excel 2007 !

- per questo esiste rangeRighe, che contiene l'informazione di dove esattamente finiscono i dati.
rangeRicerca è l'intersezione tra questi 2 insiemi, e ci da esattamente il campo di ricerca per un dato Foglio, non una cella di più, non una cella di meno.
La colonna su cui finisce rangeRighe non ha molta importanza, poteva essere anche :

Set rangeRighe = Range("A1:Z" & fineRange)

Importante è che contenga almeno tutte le colonne di rangeColonne.

- Metodo alternativo poteva essere anche definire rangeRicerca in un colpo solo :

Set rangeRicerca = Range("A1:A" & fineRange, "B1:B" & fineRange, ..., ... )

Scegli quello che ti piace di più.

Ora credo sia tutto chiaro.

[mauimaui4]

Grazie per le tue ripetizioni vba.

comunque la stringa di codice:
Set rangeRighe = Range("A1:E" & fineRange)

Mi pare piu' corretta.

Quanto al fatto che dicevi:
>>se lasciassi fare a lui mi andrebbe in search su tutte le oltre 65000 colonne.

Hai ragione infatti quando facevo le prove su un foglio praticamente vouto con:
File_To_Play = TextBox1
Set RangeObject = Cells.Find(What:=File_To_Play, After:=ActiveCell, LookAt:=xlPart)
Ci metteva qualche secondo per ogni ricarca nonostante i 4 dati inseriti nel foglio.

-----------------------------------------------------
Lo dico a chi leggera' sto post.

La differenza fra un buon codice e uno brutto e' che quello buono Fa velocemente quello che hai pensato ed e' di immediata editazione. Uno brutto fa lentamente quello che hai pensato ma fa pure cio' che non avresti mai pensato ne voluto che facesse, con risultati imprevidibili, direttamente proporzionali alla complessita' dell'elaborazione. La stessa cosa vale anche per i disegni CAD e tutti i tipi di elaborati, alla fine tutto si incontra.
------------------------------------------------------


La differenza fra pratica e teoria e' piu' vera in pratica che in teoria.


Vabbeh' avrei anche un paio di domande su altre faccende vba...... se hai tempo.............


Ti ringrazio, sono davvero contento di questa macro, sono 6 mesi che volevo sviluppare sta funzione senza successo (dallo shell script al vba non so do mettermi le mani, grep sed e awk mi sembrano un tantino piu' immediati) funzione che ti consiglio di salvarti, potra' tornarti utile come puo' essere utile a molti altri che utilizzano fogli enormi 10.000 righe 10 colonne con molti dati.

Grazie ancora

[MarcoGG]

Originariamente inviato da mauimaui4

Quanto al fatto che dicevi:
>>se lasciassi fare a lui mi andrebbe in search su tutte le oltre 65000 colonne.

Hai ragione infatti quando facevo le prove su un foglio praticamente vouto con:
File_To_Play = TextBox1
Set RangeObject = Cells.Find(What:=File_To_Play, After:=ActiveCell, LookAt:=xlPart)
Ci metteva qualche secondo per ogni ricarca nonostante i 4 dati inseriti nel foglio.

Sì beh, ho sbagliato a scrivere, naturalmente, intendevo 65000 RIGHE, non colonne.
E prova ad immaginare l'impatto su uno Sheet Excel 2007, dove le righe disponibili sono esattamente 16 volte quelle di uno Sheet Excel 2003 o precedenti... Siamo ben oltre il milione di righe...

[mauimaui4]

Ciao, spero di non disturbarti, C'e' un problema sul codice che mi avevi realizzato.
In sostanza usandolo mi sono accorto che la funzione da un errore in questo senso qui.
Posto che ho le colonne :
A B C D E

e posto che la ricerca deve interessare le sole colonne A B E

Se il cursore della active cell si trova posto su una delle colonne interessate alla ricerca ed eseguo la macro il sistema funziona correttamente, con il find next da te scritto anche per ricorrenze successive.
Tuttavia se la active cell si trova in posizione diversa dalle tre colonne in cui si svolge la ricerca, es. in C o in H il codice da errore evidenziando nell'editor di giallo la seguente riga:


Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)


poi do F5 sposto il cursore e riparte ma mi faccio due palle..........

1) E' possibile ovviare a tutto cio'?
2) Vorrei evitare che per far partire la ricerca dalla prima posizione utile in cui mi trovo con il cursore debba di farmi un offset di cella rispetto alla cella attiva per attivare prima della routine di ricerca una cella tra quelle tre in cui insiste il range della ricerca da me identificato.

Grazie per il tuo prezioso aiuto....... Non sono un programmatore ma.......
Non so se vba e' imbecille o se e' tutto normale.......

Se hai difficolta' (ma non credo, magari e' una stupidaggine) e mi da la mail ti allego un prototipo .xls tipo per farci le prove.

[MarcoGG]

Originariamente inviato da mauimaui4

Se il cursore della active cell si trova posto su una delle colonne interessate alla ricerca ed eseguo la macro il sistema funziona correttamente, con il find next da te scritto anche per ricorrenze successive.
Tuttavia se la active cell si trova in posizione diversa dalle tre colonne in cui si svolge la ricerca, es. in C o in H il codice da errore evidenziando nell'editor di giallo la seguente riga:

Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)
...
...
Grazie per il tuo prezioso aiuto....... Non sono un programmatore ma.......
Non so se vba e' imbecille o se e' tutto normale.......

Il buon VBA non ha colpe. Se si esegue rangeRicerca.Find con After:=ActiveCell, e poi vai a selezionare una cella che non è inclusa in rangeRicerca, è ovvio che vada in errore.
Quando si creano Fogli automatizzati bisognerebbe sempre prevenire gli errori dell'utente, tipicamente bloccando le celle indesiderate, che così non potranno più essere selezionate...
Comunque, la soluzione più veloce al problema, senza bloccare le celle, è questa :

Private Sub cmd_search_Click()

    If TextBox1.Text = "" Then Exit Sub
    Dim strRicerca As String
    strRicerca = TextBox1.Text
              
    Dim rangeTrovato As Range
    On Error GoTo ERRORE_SEL
    Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)
    GoTo RANGE_T
ERRORE_SEL:
    rangeRicerca.Cells(1, 1).Select
    Set rangeTrovato = rangeRicerca.Find(What:=strRicerca, After:=ActiveCell, LookAt:=xlPart)
RANGE_T:
    If Not rangeTrovato Is Nothing Then rangeTrovato.Select

End Sub

[mauimaui4]

Ok Funziona Grazie come sempre.

In effetti anche io avevo pensato di mettere in una variabile il valore della cella corrente, portare l'inizio ricerca usando la variabile numero riga ma imponendone la prima colonna A in modo che la ricerca partisse da li, e se poi la ricerca non aveva esito positivo, avrei rimesso il cursore sulla variabile memorizzata di cella corrente. Pedestre Pedestre quasi a pecoroni.
Anche tu hai posto il valore di inizio ricerca a partire dalla riga corrente ma dalla colonna A, solo che il tutto e' fatto in maniera piu' fine diretta ed elegante.


Se puoi rispondimi a queste.
1) non so se conosci office 2007 se dovessi mutuare tutte le macro che con tanta fatica ho fatto in office 2007 dovrei riscriverle in .net giusto??? e' possibile che offc2007 non legga il vba ???
Me sa tanto che il porting da vba a .net non e' ne automatizzabile ne indolore vero???? Ho timore.........
Quale linguaggio di script e' integrato in automatico in office 2007 ????


2) Mi sembra di aver letto che non e' possibile aprire una seconda userform se ne e' gia' aperta una.
Ti dico perche' mi piacerebbe che se la ricerca non ha esito positivo compaia una userform ulteriore con la userform di ricerca gia' aperta, ma a tempo, che so di 1 secondo che dica Value not found, e poi scompaia da sola.
Vorrei evitare di usare un msg_box che poi devi dargli ogni volta ok per farlo andar via, detesto gli ok e i click inutili del mouse.
Grazie.

[MarcoGG]

Originariamente inviato da mauimaui4

1) non so se conosci office 2007 se dovessi mutuare tutte le macro che con tanta fatica ho fatto in office 2007 dovrei riscriverle in .net giusto??? e' possibile che offc2007 non legga il vba ???
Me sa tanto che il porting da vba a .net non e' ne automatizzabile ne indolore vero???? Ho timore.........
Quale linguaggio di script e' integrato in automatico in office 2007 ????

Office 2007 usa VBA, come Office 2003. Piacerebbe molto anche me un Office con un bel subset del Framework .Net e VB.NET 2008 come linguaggio, ma al momento è pura utopia...
Portare codice VBA da 2003 a 2007 è semplicissimo. Se invece il senso della domanda era : portare un qualcosa scritto in Excel-VBA in un'applicazione VB.NET, è tutt'altro discorso.

Originariamente inviato da mauimaui4

2) Mi sembra di aver letto che non e' possibile aprire una seconda userform se ne e' gia' aperta una.

No, invece è possibile. Quello che non è possibile è usarle contemporaneamente.

Originariamente inviato da mauimaui4

Ti dico perche' mi piacerebbe che se la ricerca non ha esito positivo compaia una userform ulteriore con la userform di ricerca gia' aperta, ma a tempo, che so di 1 secondo che dica Value not found, e poi scompaia da sola.
Vorrei evitare di usare un msg_box che poi devi dargli ogni volta ok per farlo andar via, detesto gli ok e i click inutili del mouse.
Grazie.

Ecco una cosa che eviterei di fare, visto lo scarso supporto di VBA alle operazioni temporizzate ( e in alcuni casi "dannoso", in quanto può crashare... ). Io ci metterei una MsgBox e buonanotte, un click in più non ha mai ucciso nessuno.
Se invece vuoi perseverare dovrai usare la Application.OnTime...

[mauimaui4]

Non ho parole, grazie al tuo supporto sto creando nuove funzionalita',
sebbene non riesca a capire esattamente tutto il codice che mi scrivi, mi hai fatto superare delle situazioni di stallo ma mi hai anche dato diversi pretesti di sperimentazione.


Una domanda di merito ma come mai parlando con dei project manager mi dicevano che oggi i programmatori vba non sono piu' richiesti?
Mi hai detto che offic2007 supporta ancora il vba strano no?
Pare che con l'avvento di .net i programmtori vba non servano piu' e quei pochi che si usano servono solo per fare supporto a vecchie applicazioni, ti risulta questo?

Grazie

[MarcoGG]

Originariamente inviato da mauimaui4

Una domanda di merito ma come mai parlando con dei project manager mi dicevano che oggi i programmatori vba non sono piu' richiesti?
Mi hai detto che offic2007 supporta ancora il vba strano no?
Pare che con l'avvento di .net i programmtori vba non servano piu' e quei pochi che si usano servono solo per fare supporto a vecchie applicazioni, ti risulta questo?

Grazie

Sì, Office 2007 gira ancora su VBA ( credo sia la stessa versione 6.3 di Office 2003, più alcune aggiunte minori ).
Come già detto, personalmente mi piacerebbe moltissimo poter scrivere codice VB.NET in Office ( chiamiamolo VBA.NET ), ma forse ciò richiederebbe la riscrittura di una grossa fetta della suite Microsoft...
Secondo me, finchè ci sarà Office, ci sarà VBA, o comunque un linguaggio per eseguire automazioni. Un ipotetico Office senza possibilità di scrivere codice lo vedrei un terribile passo falso, mentre un Office con .NET un terribile passo in avanti...
Per ora teniamoci stretto VBA, che, a quanto leggo in giro, sarà presente anche in Office 2010, non che la cosa mi entusiasmi, considerato l'imbarazzante divario in fatto di potenzialità con un VB.NET 2008 / 2010, ma meglio che niente.
Inoltre VBA gira anche in molti contesti non-MS, basti pensare a colossi come Corel, AutoCAD, e molti altri...

Per quanto riguarda i "programmatori VBA", il problema non è tanto l'esserlo o meno, ma l'essere solamente uno sviluppatore VBA è decisamente riduttivo.
Teorie a parte, io il VBA lo uso spesso, ho fatto anche recentemente progetti 100% VBA, venduti a clienti, non "programmini per la sciura maria", e non lo mollo di certo, anche se il top del mio interesse è l'ambiente .NET.