Utilizzo VBA al posto della funzione CERCA.VERT ()

Anonimo
2011-07-02T09:56:44+00:00
Il file ANAGRAFICHE.xls è un file esterno alla tabella di ricerca sottostante.
ESTRAZIONE DA FILE ANAGRAFICHE.XLS
es:IN B2 c'è la funzione  =cerca.vert(A2;'C:\Desktop\EROGAZIONI 2011[ANAGRAFICHE.xls)Foglio1!'$A2:E100;1) e così ho esteso anche nelle altre celle la formula.
A B C D E
1 cognome e nome Nato Comune Esenz.Pat. Esenz. IC
2 GATTI ROBERTO 12/05/1965 MILANO SI 00/01/1900 tab. attiva
3 RAGGIO MARIO 13/06/2000 ROMA 00/01/1900 SI con funz. Cerca.vert
4 VERDI GIACOMO 25/04/1984 MILANO 00/01/1900 SI
5 ROSSI UGO 03/02/1950 MILANO 00/01/1900 SI
SU FOGLIO XLS ESTERNO DI NOME :ANAGRAFICHE.XLS
A B C D E
1 cognome e nome Nato Comune Esenz.Pat. Esenz. IC
2 GATTI ROBERTO 12/05/1965 MILANO SI tab.esterna
3 RAGGIO MARIA 13/06/2000 ROMA SI
4 ROSSI MARIO 25/04/1984 TORINO SI SI
5 ROSSI UGO 03/02/1950 MILANO SI
Purtroppo con la funzione cerca.vert(), con cognome e nome simile ma non uguale (vedi Raggio Mario oppure Verdi Giacomo
che non esiste nella tabella esterna "ANAGRAFICHE" , mi tira fuori la data più vicina, invece che dirmi che tale anagrafica non è presente.
Pertanto la tabella di estrazione da un file esterno, non è affidabile e non mi permette di accorgermi dell'errore
Si può fare in VBA Excel 2003 un codice che sostituisca la funzione cerca e quando non trova il nominativo
in luogo del dato più prossimo mi scriva in cella( "Manca anagrafica") ?<br><br><br>Se invece con la correzione della formula CERCA,VERT() si può risolvere il problema, ben venga.<br><br><br>Ho letto un quesito simile, ho cercato di adattarlo ma non ho capito perchè non mi funziona.
Microsoft 365 e Office | Excel | Per la casa | Windows

Domanda bloccata. Questa domanda è stata eseguita dalla community del supporto tecnico Microsoft. È possibile votare se è utile, ma non è possibile aggiungere commenti o risposte o seguire la domanda.

0 commenti
Risposta accettata dall'autore della domanda
Anonimo
2011-07-15T07:43:18+00:00

Accidenti, oggi ho scoperto che anche le lettere minuscole accentate si possono rendere accentate in maiuscolo senza utilizzare l'apostrofo(apice).

Pensa che non so come si ottiene da tastiera la lettera accentata PATTÈ, l'ho dovuta copiare e incollare altrimenti l'avrei scritta PATTE' come avrebbero fatto in molti.

Quindi, fin qui, tutto OK. interviene sia sulla colonna A del Cognome e anche sulla  C della Residenza.

Ho cercato di scoprire cosa potesse fare il tuo codice riga per riga ma non essendoci un commento ci ho rinunciato, non posso dire che ho fatto un atto di fede perchè ho voluto provarla mettendo blank davanti ... in fondo, mettendo lettere minuscole intercalate a maiuscole e con accenti finali ma funziona alla grande. Direi eccezionale, ma sempre con il tarlo di prendere il pacchetto così com'è.

Pertanto fino qui, OK. Se vuoi darmi in pasto un altro pezzo di codice per proseguire, lo aspetto con ansia.

Intanto commento la parte che immagino risulti *ostica* del codice precedente:

    With sh

        'trovo l'ultima cella con un valore in colonna A

        lRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'definisco il range con nomi e cognomi

        Set rng = .Range("A2:A" & lRiga)

        'ciclo in range; per ogni cella nel range

        For Each c In rng

            'pulisco le due variabili

            s = ""

            v = ""

            'in c

            With c

                'divido la stringa della cella in tante parti

                'utilizzando lo spazio " " come separatore

                v = Split(.Value, " ")

                'per ogni parte ottenuta

                For lng = 0 To UBound(v)

                    'se la parte è diversa da stringa vuota

                    If v(lng) <> "" Then

                        'aggiungo la parte alla variabile s

                        s = s & v(lng) & " "

                    End If

                Next

                'modifico il valore della cella

                'mettendo tutto maiuscolo la stringa s

                'ed eliminando l'ultimo carattere

                '(nello specifico è uno spazio che so

                'di avere nella stringa)

                .Value = UCase(Mid(s, 1, Len(s) - 1))

                'pulisco le variabili

                s = ""

                v = ""

                'divido la stringa della colonna C

                '(Offset(0,2)

                v = Split(.Offset(0, 2).Value)

                'ripeto quanto fatto per la colonna A

                For lng = 0 To UBound(v)

                    If v(lng) <> "" Then

                        s = s & v(lng) & " "

                    End If

                Next

                .Offset(0, 2).Value = UCase(Mid(s, 1, Len(s) - 1))

            End With

        Next

    End With

Per le accentate, vedi qui: http://www.asciitable.it/asciiext.asp

ALT+0192 ti darà À, ecc.

Vedo di andare avanti... ;-)

La risposta è stata utile?

1 persona ha trovato utile questa risposta.
0 commenti
Risposta accettata dall'autore della domanda
Anonimo
2011-07-15T08:42:11+00:00

 

Vedo di andare avanti... ;-)

... e avanti andiamo.

Questa(che deve essere eseguita *DOPO* aver fatto girare la prima), ti lascierà le righe con Nome/cognome, Data di nascita e residenza *UNIVOCI*. Vuol dire che se Mario Rossi compare tre volte con gli stessi dati, te ne lascia uno solo. Se la data o la residenza di uno dei mario Rossi è diversa, te ne lascierà due:

Public Sub mEliminaDoppi()

    Dim lng As Long

    Dim lRiga As Long

    Dim sh As Worksheet

    Dim lRip As Long

    Set sh = ThisWorkbook.Worksheets("Foglio1")

    Application.ScreenUpdating = False

    With sh

        'trovo l'ultima cella con un valore in colonna A

        lRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'aggiungo una colonna

        .Range("A:A").Insert Shift:=xlToRight

        'concateno le celle B:D(ho una colonna in più

        'e la residenza è in D)

        .Range("A2").Value = "=CONCATENATE(B2 & C2 & D2)"

        'faccio l'auto completamentio in colonna A

        .Range("A2").AutoFill Destination:=Range("A2:A" & lRiga)

        'ciclo le righe partendo dall'ultima

        For lng = lRiga To 2 Step -1

            'conto quante volte ho la stessa cosa in A

            lRip = Evaluate("=COUNtIF(" & "A2:A" & lRiga & "," & _

                """" & .Range("A" & lng).Value & """" & ")")

            'se ne ho più di una, elimino la riga

            If lRip > 1 Then

                .Rows(lng & ":" & lng).Delete Shift:=xlUp

            End If

        Next

        'elimino la colonna A che non mi serve più

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

    End With

    Application.ScreenUpdating = True

    Set sh = Nothing

End Sub

Fai alcune prove su copie del file originale e vedi se va bene nel tuo contesto.

La risposta è stata utile?

0 commenti

39 risposte aggiuntive

Ordina per: Più utili
Più utili Più recente Meno recente
  1. Anonimo
    2013-06-20T17:27:21+00:00

    Chiedo scusa,

    avrei un problema analogo e vorrei sapere se posso usare la stessa procedura.

    In una cartella di lavoro ho due fogli.

    Nel Foglio 1 ho una tabella contenente circa 32000 serial number che utilizzo per lavoro.

    Nel foglio 2 ho un altra tabella contenente sempre serial number con la descrizione del materiale.

    Vorrei che ogni serial number del primo foglio venga confrontato con quelli del secondo foglio e quando viene trovata una corrispondenza nel Foglio 1 venga copiata la descrizione del materiale presente nel secondo foglio.

    Provo con l'esempio:

    Foglio 1

           A

    12313198797

    67868686876

    87987896660

    66869866876

    Foglio 2

            A                   B

    12313198797    maniglia

    66869866876    guarnizione

    89789778909

    87878897788

    vorrei che i dati nella colonna B del Foglio 2 vengano copiati nella colonna B del foglio 1 quando vengono trovato due serial number uguali.

    Ho provato con la funzione cerca vert ma senza risultati affidabili.

    Potreste aiutarmi?

    La risposta è stata utile?

    0 commenti
  2. Anonimo
    2011-07-25T16:36:26+00:00

    Si Mauro, se in ANAGRAFICHE_E_PATOLOGIE.xls (foglio3) compare Rossi Mario 1 e Rossi Mario 2 anche su ANAGRAFICHE.xls ci sarà Mario Rossi 1 e Mario Rossi 2.

    Tuttavia su ANAGRAFICHE.xls potrebbe accadere che uno dei due non ci sia per errore di un mancato aggiornamento. Ecco perchè, qualora un nominativo dovesse mancare su ANAGRAFICHE.xls e quindi non ci sarebbe nemmeno Anno residenza e patol1 patol2 (per quel nominativo mancante), io attualmente in ANAGRAFICHE_E_PATOLOGIE.xls nel campo anno e residenza facevo apparire la dicitura NO ANAGR.

    Con questa evidenza, colui che non ha digitato per incuria ad esempio Rossi Mario 1, avrebbe aperto il foglio ANAGRAFICHE.xls, avrebbe preso i dati dal cartaceo con le informazioni di quel nominativo, l'avrebbe inserite e facendo girare nuovamente il file ANAGRAFICHE_E_PATOLOGIE.xls, quell'errore contrassegnato con NO ANAGR. si sarebbe sistemato e sarebbe apparso a fianco Rossi Mario 1 , l'anno di nascita - la residenza la patol1 e patol2.

    Quindi riassumendo, le anagrafiche in tutte e due i files xls. sarebbero state identiche o al limite max mancanti in ANAGRAFICHE.xls.

    Il disguido poteva  nascere per colpa di un eventuale blank in coda al cognome e nome o in un file o nell'altro proprio perchè invisibile ad occhio nudo e allora avremmo potuto avere un mancato abbinamento come se in ANAGRAFICHE.xls quel nome con il blank pur essendo Rossi Mario 1 in entrambi gli elenchi in realtà erano diversi per via del blank.

    La normalizzazione dei blank a questo punto andrà fatta su entrambi gli elenchi prima di ogni cosa sul cognome e nome. Se poi ci sarà un blank nella residenza, non essendo una colonna interessata alla comparazione, visto che i due elenchi non hanno le stesse colonne tranne il cognome e nome, non sarà determinante.

    Così facendo, il file ANAGRAFICHE_E_PATOLOGIE.xls (foglio3) tramite il tuo codice, si arricchirà dei dati mancanti prendendoli dal file ANAGRAFICHE.xls (per dati mancanti intendo   anno - residenza - patol1 patol2

    La risposta è stata utile?

    0 commenti
  3. Anonimo
    2011-07-25T15:22:54+00:00

    Ok. Mauro, hai ragione, è una carenza quella dell'omonimia che fin dall'inizio non è stata prevista in qanto i vari nominativi non erano collegati con un anno di nascita ed una città.

    Qualora ci fosse stato un omonimo si attribuiva un contrassegno previsto sul documento cartaceo ad esempio Rossi Mario 2°.

    Quindi, nell'elenco ANAGRAFICHE.xls ci sarebbero stati due Rossi Mario (ossia Rossi Mario e Rossi Mario 2° ed ognuno dei due con il proprio anno di nascita e residenza)

    Una cavolata se vogliamo ma è così. Quindi, anche in ANAGRAFICHE_E_PATOLOGIE.xls, avremmo avuto due Rossi Mario (Rossi Mario e Rossi Mario 2°), pertanto l'univocità avveniva in questo modo e non basato su ANNO e RESIDENZA.

    Tu, ovviamente, sei andato oltre e reputi molto probabilmente inconcepibile questo artifizio, ma ti assicuro che ad oggi non si è verificato un omonimia che comunque potrebbe accadere, ma scongiurata con il sistema che ti ho detto.

    A me preoccupava di più la pulizia dei blank finali in quanto subdoli e che non si vedevano e comunque importanti in quanto lo stesso nominativo non si abbianava proprio per un blank di troppo.

    Tu nella risoluzione sei andato ben oltre testando anche l'anno di nascita e la residenza.

    Purtroppo su ANAGRAFICHE_E_PATOLOGIE.xls. (foglio3) tali informazioni (anno e residenza) le catturava con CERCA.VERT() e comunque ha sempre funzionato proprio perchè l'escamotage è stato quello di aggiungere al cognome e nome quel 2° e ovviamente il Rossi Mario 2° avrebbe avuto il suo anno di nascita e la sua residenza diversa dal Rossi Mario) nell'elenco ANAGRAFICHE.xls.

    Ora sai tutto i difetti e le carenze di un lavoro che so per certo immodificabile nella sua struttura per diverse ragioni.  Che altro dire, Anzi, non mi resterà  che continuare con il CERCA.VERT() formula anzichè VBA:

    Grazie per la pazienza. Non me la sento di chiedertidi scrivere il codice anche se so che ogni nome che apparirà sui due elenchi (ANAGRAFICHE.xls e ANAGRAFICHE_E_PATOLOGIE.xls (foglio3)  ho la certezza che siano  UNIVOCi.

    Univoci alla loro maniera ma comunque univoci (non per anno di nascita e residenza ma perchè l'univocità è insita nel cognome e nome con quel distinguo (.Rossi Mario 2°)

     

     

    Se(se) abbiamo Mario Rossi 1, Mario Rossi 2, ecc., ripetuti un unica volta, per me sono univoci. Ma devono essere univoci su *entrambi i fogli*. Cioè Mario Rossi 1 deve essere presente su tutti e due i fogli con quella dicitura. Se così è, possiamo rinunciare valutare residenza/anno. E' dunque così?

    La risposta è stata utile?

    0 commenti