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 |