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
    2011-07-22T06:21:21+00:00

    Grande Mauro, solo ora mi sono reso conto come si comporta il tuo codice. Mi ero limitato alla lettura dei tuoi commenti che hai riporrtato sopra il codice dei doppi.

    Ma eseguendo questo codice non mi ero mai accorto del meccanismo di concatenamento con la data e la residenza. La velocità di esecuzione non me lo aveva fatto notare.

    Eseguendolo passo dopo passo tramite F8 ho avuto modo di notarlo.

    Quindi, per eliminare i doppi utilizzi la colonna A del foglio 1 ANAGRAFICHE.xls.

    Mi domandavo, visto che il confronto tra le anagrafiche complete di anno e residenza e patologie che sono presenti in col. A del suddetto file, ora si dovranno confrontare con il solo cognome e nome presente in col. A3 e via via sotto di questa colonna del file ANAGRAFICHE_E_PATOLOGIE.xls, se tu intendi fare una elaborazione simile sul foglio3 di ANAGRAFICHE_E_PATOLOGIE.xls non ci dovrebbero essere problemi.

    Comunque, Mauro, come al solito hai ragione di dire che non avevo capito, ma ora vedendo la genialità del concatenamento e che non ero riuscito a notare, era come parlare con un sordo.

    Grazie per il tempo che mi stai dedicando, puoi procedere anche sul foglio3 in tal modo, tenendo in considerazione che per il confronto, sul foglio3 di ANAGRAFICHE_E_PATOLOGIE.xls avrai solo il cognome e nome della persona, quindi senza anno e residenza.

    Quindi, oltre a recuperare queste informazioni, anno-residenza, dovrai recuperare anche la patol1 e patol2 di quel nominativo. Qualora mancasse tale nominativo sul foglio1 ANAGRAFICHE.xls dovrà apparire in luogo dell'anno e della residenza la scritta NO ANAGR.

    La risposta è stata utile?

    0 commenti
  2. Anonimo
    2011-07-21T17:37:36+00:00

    <<Va bene se lascio la colonna A(Nome/Anno/CIttà) sul Foglio1 del primo file(posso anche nasconderla) e utilizzo una colonna similare nel Foglio3 del secondo file(posso nascondere anche quella)? >>

    IN col. A del file ANAGRAFICHE.xls abbiamo solamente i nomi (l'anno è in col.B) la città è in col C ecc.ecc..

    Ma nel foglio3 del secondo File, ANAGRAFICHE_E_PATOLOGIE.xls posso avere 10-100-300-nomi a seconda della chiave di un'estrazione precedente e che se chi lavora il file ANAGRAFICHE.xls ha fatto il suo dovere, non ci dovrebbero essere diciture del tipo NO ANAGR: perchè saranno certamente individuati nome per nome e completati con i dati (ANNO-RESIDENZA-PATOL1-PATOL2) presenti nel file ANAGRAFICHE.xls

     

     

    Non hai capito. Prova a mettere un commento a questa riga di codice:

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

     nella macro:

    Public Sub mEliminaDoppi()

    così vedi cosa intendo io per colonna A e per indice univoco. Vedrai la colonna A che contiene Nome/Anno/Residenza tutte di seguito. E' quello che mi serve per rendere univoci i dati in Foglio1. Se non ho una cosa tipo questa(un indice univoco), diventa quasi impossibile fare quello che chiedi in Foglio3.

    La risposta è stata utile?

    0 commenti
  3. Anonimo
    2011-07-21T14:23:41+00:00

    Ciao Mauro,

    hai ragione, cercherò di essere più preciso.

    La colonna A del file ANAGRAFICHE.xls conterrà anche grazie al tuo contributo i cognomi univoci con a fianco in col. B -C-D-E le altre informazioni.  Questo file, rappresenta tutta l'anagrafica (chiamiamola generale di questo lavoro), purtroppo gestita a mano con tutti gli errori del caso (tipo Blank in fondo ecc.ecc.)

    Mentre nel foglio3 di ANAGRAFICHE_E_PATOLOGIE.xls a partire da A3, avremo un di cui , dei nomi presenti nel file ANAGRAFICHE.xls anch'essa composta già da nomi univoci

    Quindi, mentre prima usavo la funzione CERCA.VERT() per recuperare a fianco di ogni nominativo del file ANAGRAFICHE_E_PATOLOGIE.xls (foglio3 - a partire da A3) LE ALTRE INFORMAZIONI, QUALI,  - anno in col.B -  residenza in col. C - patol1  in col. D - patol2 in col. E, ora si tratta di recuperare queste informazioni presenti solamente nel file ANAGRAFICHE.xls ma tramite codice VBA.

    Quindi i nomi che mi ritrovo in col.A del file ANAGRAFICHE_E_PATOLOGIE.xls provengono da un'altra elaborazione e saranno sempre e comunque  un di cui del precedente file (A PATTO CHE CHI TIENE AGGIORNATO QUEL FILE ANAGRAFICHE.xls LO FACCIA CON COSTANZA). Ecco perchè, qualora ci fosse un nominativo nel file ANAGRAFICHE_E_PATOLOGIE.xls che per caso non sia presente nel file ANAGRAFICHE.xls (per negligenza dell'operatore, altrimenti non si dovrebbe mai verificare), dovrà apparire in col.B e col. C la dicitura NO ANAGR.

    Il codice che mi fornirai sarà residente sul file ANAGRAFICHE_E_PATOLOGIE.xls e avrà lo scopo di completare le informazioni mancanti a fianco ai nomi che sono stati estratti da un'altra elaborazione.

    Quando tu dici:

    <<Va bene se lascio la colonna A(Nome/Anno/CIttà) sul Foglio1 del primo file(posso anche nasconderla) e utilizzo una colonna similare nel Foglio3 del secondo file(posso nascondere anche quella)? >>

    IN col. A del file ANAGRAFICHE.xls abbiamo solamente i nomi (l'anno è in col.B) la città è in col C ecc.ecc..

    Ma nel foglio3 del secondo File, ANAGRAFICHE_E_PATOLOGIE.xls posso avere 10-100-300-nomi a seconda della chiave di un'estrazione precedente e che se chi lavora il file ANAGRAFICHE.xls ha fatto il suo dovere, non ci dovrebbero essere diciture del tipo NO ANAGR: perchè saranno certamente individuati nome per nome e completati con i dati (ANNO-RESIDENZA-PATOL1-PATOL2) presenti nel file ANAGRAFICHE.xls

    Io direi, di tentare, perchè capisco che non sono un bravo spiegatore, e devo basarmi molto sul tuo intuito di ciò che vado chiedendo. Ecco perchè ho ripreso gli esempi nel post precedente.  Infatti, in ANAGRAFICHE.xls dell'esempio abbiamo 4 nominativi; mentre sul file ANGRAFICHE_E_PATOLOGIE.xls ne abbiamo solo 3 e per giunta il nominativo GRULLO non è presente nel file ANAGRAFICHE.xls.

    Quindi il codice dovrà aggiornare sul file ANAGRAFICHE_E_PATOLOGIE.xls con  i dati (anno-residenza-patol1-patol2) e siccome GRULLO non è presente in ANAGRAFICHE.xls dovrà essere scritta in col. B e C la dicitura No ANAGR.

    Questo implicherà che l'operatore si ccorgerà di non aver aggiornato con il nominativo GRULLO  il file ANAGRAFICHE.xls e correrà ai ripari, l'aggiornerà e quindi, facendo ripartire il tuo codice otterrò l'elenco ANAGRAFICHE_E_PATOLOGIE.xls completo e pronto per la stampa.

    La risposta è stata utile?

    0 commenti