Controllare dati doppi con sostituzione del dato duplicato con dati presenti su altra cartella chiusa.

(23/03/03)

L'esercizio:

  • In due colonne di una tabella (che chiameremo Tab1 per nostra comodità di riferimento) su un foglio di lavoro, (colonna A e B, per esempio) si possono trovare, per ogni riga, due valori eguali nelle due colonne.

  • Vogliamo controllare la colonna B: se il valore presente sarà uguale al valore presente nella stessa riga della colonna A, vogliamo che il valore doppio della colonna B venga sostituito con il valore presente in un'altra tabella (che chiameremo TabOrigine) posta sul Foglio1 di una altra cartella chiusa presente sul nostro HD.

  • Nella cartella chiusa è presente un elenco su più colonne (TabOrigine) : la prima colonna (a sinistra, la A), contiene valori univoci uguali ai valori presenti nella colonna A della Tab1. Vorremo quindi reperire il valore correlato posto nella seconda colonna ( la B ) della TabOrigine, e sostituirlo al valore doppio presente nella colla B della Tab1. Per fare questo sfrutteremo la Funzione CERCA.VERT posta all'interno del nostro ciclo di ricerca dati doppi.

  • Il ciclo eseguirà un controllo dei valori presenti in ogni riga della colonna B della Tab1; nel caso che il valore a lato (colonna A) sia uguale, si effettua la funzione CERCA.VERT e si sostituisce il valore così trovato. Per evitare che Excel, trattandosi di un "collegamento" ad un altro foglio (e chiuso), presenti la finestra di conferma provenienza dati, inseriamo un semplice : Application.DisplayAlerts = False

Sotto le immagini delle due tabelle; i nomi e i valori sono d'esempio.

Tab1 TabOrigine

Come vediamo in Tab1, esistono valori uguali nelle righe 1, 2. 4, 6, 10. La routine controllerà i valori nelle due colonne, e nel caso siano uguali svolgerà la Funzione CERCA.VERT sulla TabOrigine andando a "pescare" il dato correlato al valore uguale al dato cercato nella colonna B (colore). Faremo inoltre controllare se il valore presente nella colonna B di Tab1 è diverso dal valore della stessa riga colonna A: in questo caso faremo scrivere in B la parola "Sconosciuto". Facciamo anche controllare se le celle della colonna B e rispettiva cella della colonna A saranno vuote: in questo caso passeremo alla riga successiva fino al termine del ciclo. Nel caso di dati doppi nella Tab1, ma il cui valore non venga trovato nella TabOrigine, nella cella colonna B apparirà il messaggio #N/D : valore non disponibile.

E questo è il risultato prodotto dalle nostre istruzioni nella Tab1:

e queste le istruzioni. Come si nota, nella Funzione CERCA.VERT (in inglese VLOOKUP), si deve indicare il percorso dove risiede il file con la TabOrigine. Attenzione alla sintassi.

Sub TrovaeSostituisci()
Dim CL As Object

'sotto: indichiamo al ciclo For Each CL (per ogni cella) su quale Range agire
For Each CL In Range("B1:B10")

'se la cella è vuota passa a Next (successivo)
If CL.Value = "" Then GoTo 10
'se il valore della cella è uguale al valore della cella a destra (colonna A)
If CL.Value = CL.Offset(0, -1).Value Then
'sotto: evita l'apparizione della  finestra di conferma
Application.DisplayAlerts = False

'allora nella cella (CL) poni la funzione cerca.vert (che inizia la ricerca in TabOrigine a 'partire dalla quinta riga colonna A, fino alla riga 65530 colonna B, e riporta il valore contenuto nella seconda colonna (2)
CL.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Archivio\[Dati.xls]Foglio1'!R5C1:R65530C2,2,FALSE)"
'sotto: se invece il valore nella celle B è diverso dalla cella in A (CL.Offset(0, -1)
ElseIf CL.Value <> CL.Offset(0, -1).Value Then

'allora mi scrivi nella cella B la parola "SCONOSCIUTO"
CL.Value = "SCONOSCIUTO"
'sotto: se invece (ancora) le due celle sono vuote, esci dalla routine. ATTENZIONE  a 'questa istruzione: lo scopo è quello di far terminare il ciclo se saremo a fine elenco, 'istruzione necessaria specie se abbiamo impostato un Range lungo, e vogliamo appunto 'uscire quando saranno finiti i dati, altrimenti il ciclo prosegue fino alla fine anche se 'trovasse celle vuote, che per il codice vengono viste "uguali". E' NECESSARIO quindi 'NON lasciare righe vuote in A e in B se esistono dati nelle righe successive perchè la 'routine terminerebbe senza controllare i dati sottostanti.
ElseIf CL.Value = "" And CL.Offset(0, -1).Value = "" Then
Exit Sub
End If
10:
Next
End Sub

E' evidente che se i dati da riportare (TabOrigine) non fossero su un'altra cartella, ma sulla stessa cartella aperta dove risiede la Tab1, sarà ancora più semplice e basterà modificare il percorso che mira alla TabOrigine; quindi se questa fosse sulla stessa cartella sul foglio2, basterà scrivere:

CL.FormulaR1C1 = "=VLOOKUP(RC[-1],Foglio2!R5C1:R65530C2,2,FALSE)"

e non sarebbe più necessaria l'istruzione:  Application.DisplayAlerts = False

Buon lavoro.

prelevato sul sito http://ennius.interfree.it