Automatizzare cerca.vert con una macro
Patty
sto provando a creare una macro VBA che mi automatizzi una serie di
passaggi che attualmente faccio usando la formula cerca verticale.
Ho 2 fogli di lavoro, "Ordine" e "1". In Ordine ho i codici "madre" di
alcuni articoli, che devo convertire in codici figli, secondo le
corrispondenze presenti nel foglio "1".
Quello che vorrei ottenere è che la macro compia i seguenti passaggi:
1) per tutti i codici articoli presenti nella colonna A del foglio
"Ordine" (partendo da A6 e giù fino in fondo), mi faccia un cerca.vert
nel foglio "1" che mi restituisca (sovrascrivendo gli eventuali valori
già presenti):
su A stessa di "Ordine" --> il valore corrispondente presente nella
colonna C di "1"
in B di "Ordine" --> il valore in D di "1"
in C di "Ordine" --> il valore in E di "1"
in U di "Ordine" --> il valore in F di "1"
2) se non il cerca verticale non trova una corrispondenza nel foglio
"1", per tale articolo deve lasciare tutto com'è, salvo indicare "non
trovato" nella colonna U.
Spero di essere stata chiara e che qualcuno mi voglia dare una mano...
Eventualmente posso postare un file d'esempio su internet, giusto per
semplificare il tutto! Vi ringrazio per l'aiuto....
eliano
Ciao Patty.
Sicuramente tu sei stata chiarissima, ma io non ho capito, ovviamente
per mio difetto.
Mi riferisco a:
> su A stessa di "Ordine" --> il valore corrispondente presente nella
> colonna C di "1"
e mi sembra singolare che tu vada a sovrascrivere l'elemento di
ricerca esistente sul file Ordine in colonna A, utilizzato nel
CercaVerticale, con un elemento reso disponibile dal CercaVerticale
basato su quell'elemento di ricerca.
Non è che la cosa non sia fattibile, ti pregherei solo di darmi
conferma di questo modo di procedere un po' fuori del comune.:-))
[anche fuori Provincia, credo] :-D
Grazie e saluti
Eliano
Patty
> Mi riferisco a:
>
>> su A stessa di "Ordine" --> il valore corrispondente presente nella
>> colonna C di "1"
>
> e mi sembra singolare che tu vada a sovrascrivere l'elemento di
> ricerca esistente sul file Ordine in colonna A, utilizzato nel
> CercaVerticale, con un elemento reso disponibile dal CercaVerticale
> basato su quell'elemento di ricerca.
Si, riconosco che è strano e che va contro tutte le norme del buon
senso, però per ora è esattamente ciò che mi serve! Dei codici madre non
so cosa farmene, e per eventuali verifiche incrociate terrei una
versione del file pre-elaborazione.
Piuttosto mi piacerebbe fare in modo che il foglio in cui cerca.vert va
a pescare i dati (fin'ora chiamato "1"), venisse inserito nella macro
come una variabile, in maniera tale che sia l'utente, al momento di
lanciare lo script, a decidere (su richiesta di un msgbox) in quale
foglio fare la ricerca. (esempio "1","2" o "3"). Anche stavolta spero di
essere stata chiara...
Intanto ti ringrazio per la pazienza nell'avermi risposto!
r
> > Mi riferisco a:
>
> >> su A stessa di "Ordine" --> il valore corrispondente presente nella
> >> colonna C di "1"
>
> > e mi sembra singolare che tu vada a sovrascrivere l'elemento di
> > ricerca esistente sul file Ordine in colonna A, utilizzato nel
> > CercaVerticale, con un elemento reso disponibile dal CercaVerticale
> > basato su quell'elemento di ricerca.
>
> senso, per per ora esattamente ci che mi serve! Dei codici madre non
> so cosa farmene, e per eventuali verifiche incrociate terrei una
> versione del file pre-elaborazione.
>
> Piuttosto mi piacerebbe fare in modo che il foglio in cui cerca.vert va
> a pescare i dati (fin'ora chiamato "1"), venisse inserito nella macro
> come una variabile, in maniera tale che sia l'utente, al momento di
> lanciare lo script, a decidere (su richiesta di un msgbox) in quale
> foglio fare la ricerca. (esempio "1","2" o "3"). Anche stavolta spero di
> essere stata chiara...
>
> Intanto ti ringrazio per la pazienza nell'avermi risposto!
qui trovi 2 file (versione patty2003.xls e patty2007.xlsm)
https://sites.google.com/site/e90e50/scambio-file
vedi un po' se ho capito tutto ...
saluti
r
eliano
>
> vedi un po' se ho capito tutto ...
> saluti
>
> - Mostra testo citato -
Ciao Roby.
Ieri avevo cominciato a scrivere la consueta artigianale ed avevo
chiesto lumi in merito ad una strana cosa; mi aveva risposto
determinata confermando le sue strane esigenze.
Avevo cercato di dissuaderla, ma Donna Patty sembra determinata ed
aliena dall'ascoltare consigli più o meno equini.:-))
Visto che tu l'hai servita a puntino, ormai che l'ho scritta, gliela
propino, confermandole ancora una volta l'opinione "del giorno
prima".:-))
Public Sub prova()
Dim shO As Worksheet, sh1 As Worksheet
Dim rngO As Range, rig As Variant, urig As Long
Dim rng1 As Range, R As Long
Dim dato, dato1, dato2, dato3, dato4
Set shO = ThisWorkbook.Sheets("Ordine")
urig = shO.Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set rngO = shO.Range("A6:A" & urig)
Set sh1 = ThisWorkbook.Sheets("1")
urig = sh1.Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set rng1 = sh1.Range("A1:F" & urig)
For Each rig In rngO
R = rig.Row
On Error Resume Next
dato = WorksheetFunction.VLookup(rig, rng1, 1, False)
If Err.Number = 1004 Then
shO.Cells(R, "U").Value = "Non trovato"
Else
dato1 = WorksheetFunction.VLookup(rig, rng1, 3, False)
dato2 = WorksheetFunction.VLookup(rig, rng1, 4, False)
dato3 = WorksheetFunction.VLookup(rig, rng1, 5, False)
dato4 = WorksheetFunction.VLookup(rig, rng1, 6, False)
shO.Cells(R, "A").Value = dato1
shO.Cells(R, "B").Value = dato2
shO.Cells(R, "C").Value = dato3
shO.Cells(R, "U").Value = dato4
End If
Next
End Sub
E non bestemmiare se è una artigianale, se fo un uso dell'errore in
maniera bestiale e se non è nè completa nè stringata.
Saluti fumogeni
Eliano
r
>
> - Mostra testo citato -
è mia personale opinione che i consigni che diamo alle donzelle per il
90% dei casi non vengono nemmeno valutati ... sono talmente convinte
che un uomo dica solo sciocchezze che rispondono di conseguenza ...
il rimanente 10% ... quelle che ascoltano ... difficilmente diranno
che hai ragione, faranno tesoro del consiglio facendolo passare come
una loro idea :-)
io non ho sovrascritto il valore nel mio file ... ovviamente è
inutile :-)
la tua artigianale è ottima come sempre, anche se io preferisco usare
il conta.se per verificare un successivo cerca.vert ... ma è solo
questione di gusti ben poco è il guadagno in velocità :-)
Aug Mitico! ... e puf puf puf da tutta la tribu
r
Patty
>> - Mostra testo citato -
>
> è mia personale opinione che i consigni che diamo alle donzelle per il
> 90% dei casi non vengono nemmeno valutati ... sono talmente convinte
> che un uomo dica solo sciocchezze che rispondono di conseguenza ...
> il rimanente 10% ... quelle che ascoltano ... difficilmente diranno
> che hai ragione, faranno tesoro del consiglio facendolo passare come
> una loro idea :-)
ehehe che risate che mi fate fare ragazzi! La seconda parte è verissima,
per la prima però voi siete un'eccezione: so benissimo che non dite
sciocchezze! :-)
In ogni caso il codice di Eliano funziona perfettamente, è molto simile
in sostanza a quello che provavo a scrivere io, ma ha lo svantaggio di
non chiedermi in quale foglio fare la ricerca.
Quello di r invece mi restituisce sempre "non trovato"... :(
Ora vorrei unire il meglio dei due contributi, infatti mi sono messa a
studiare l'implementazione dell'user form (che non avevo mai usato, mi
limitavo al semplice InputBox) per definire la variabile "foglio in cui
andare a pescare i codici". Per ora con scarso successo (mi sembra un
gioco di scatole cinesi!), ma il mio obiettivo è imparare e
perfezionarmi! Intanto vi ringrazio tantissimo entrambi, il vostro aiuto
è stato utilizzimo... ora mi ci metto sotto e vi faccio sapere cosa
riesco a tirarne fuori!
eliano
Ciao Patty.
Ho dato un'occhiata al lavoro di Roby che appare molto professionale
ed abbastanza modificabile per i tuoi scopi; ti consiglio vivamente di
seguire quella strada.
Per quanto riguarda lo svantaggio della mia artigianale, accelero
usando il tuo InputBox ed alla prima curva mi accodo:
Public Sub prova()
Dim shO As Worksheet, sh1 As Worksheet
Dim rngO As Range, rig As Variant, urig As Long
Dim rng1 As Range, R As Long, shn As String
Dim dato, dato1, dato2, dato3, dato4
Set shO = ThisWorkbook.Sheets("Ordine")
urig = shO.Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set rngO = shO.Range("A6:A" & urig)
shn = InputBox("Immettere nome foglio Articoli", , 1)
Set sh1 = ThisWorkbook.Sheets(shn)
urig = sh1.Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set rng1 = sh1.Range("A1:F" & urig)
For Each rig In rngO
R = rig.Row
On Error Resume Next
dato = WorksheetFunction.VLookup(rig, rng1, 1, False)
If Err.Number = 1004 Then
shO.Cells(R, "U").Value = "Non trovato"
Else
dato1 = WorksheetFunction.VLookup(rig, rng1, 3, False)
dato2 = WorksheetFunction.VLookup(rig, rng1, 4, False)
dato3 = WorksheetFunction.VLookup(rig, rng1, 5, False)
dato4 = WorksheetFunction.VLookup(rig, rng1, 6, False)
shO.Cells(R, "A").Value = dato1
shO.Cells(R, "B").Value = dato2
shO.Cells(R, "C").Value = dato3
shO.Cells(R, "U").Value = dato4
End If
Next
End Sub
Per l'implementazione del lavoro di Roby prova a mettere il nome del
foglio direttamente in una cella del Foglio Ordine, anche per ovvi
motivi di controllo, visto che elimini con atto criminale tutte le
immissioni manuali.:-))
Saluti
Eliano
r
forse ho capito male ... anzi sicuramente!
me ne sono accorto leggendo l'artigianale di eliano ...
quando dici:
[su A stessa di "Ordine" --> il valore corrispondente presente nella
colonna C di "1" ]
io avevo capito che dovevi confrontare Ax di "Ordine" con Cx di
"1" ... da qui anche la considerazione su "le donzelle ... etc. etc."
ma accipicchia ... avevo dimenticato la regola prima ... ovvero che il
100% degli uomini ... va beh lasciamo stare :-)
mea culpa ... mea culpa ...
ma se non è così ... tu vuoi confrontare Ax di "Ordine" con Ax di "1"
e se c'è corrispondenza scrivi in Ax di "Ordine" con Cx di "1"?
mamma mia mi si è agrovigliata la lingua ...
dimmi che ora ho capito :-)
saluti
r
p.s.
comunque eliano credo ti abbia confezionato un kit a prova di ...
boooom ... ops
puf puf puf
eliano
>
> - Mostra testo citato -
Ciao Roby, ma che fai ? Mi prendi per il sellino ?? [Chiarimento: per
sellino si intende (per estensione) quello della bicicletta. Vedi mai
con questi Milanesi] :-))
Credo che tu abbia capito ed è per quello che glielo avevo
sconsigliato, ma Patty....non scende mai a patti. E questo potrebbe
essere un pregio.;-)
Saluti fumogeni con annessi fuochi artificiali,
Eliano
r
si lo è ... poi la ragazza dice che si arrangerà da sola a
sistemare ... quindi doppiamente apprezzata :-)
>
> Saluti fumogeni con annessi fuochi artificiali,
> Eliano- Nascondi testo citato
ricambio ... e chiudo la tenda ...
sogni d'oro artificiere
puf puf puf
Patty
> Per quanto riguarda lo svantaggio della mia artigianale, accelero
> usando il tuo InputBox ed alla prima curva mi accodo:
> shn = InputBox("Immettere nome foglio Articoli", , 1)
> Set sh1 = ThisWorkbook.Sheets(shn)
E' esattamente la stessa identica soluzione a cui sono approdata io.
Veloce, snella e efficace! In più ho aggiunto un if che mi restituisce
un msgbox d'errore se il numero inserito non è nel range 1:4 e fa un
exit sub se l'utente clicca su annulla. Sono felicissima del risultato.
Grazie mille ragazzi siete stati ancora utilissimi e gentilissimi!
r
> > Per quanto riguarda lo svantaggio della mia artigianale, accelero
> > usando il tuo InputBox ed alla prima curva mi accodo:
> > shn = InputBox("Immettere nome foglio Articoli", , 1)
> > Set sh1 = ThisWorkbook.Sheets(shn)
>
> E' esattamente la stessa identica soluzione a cui sono approdata io.
> un msgbox d'errore se il numero inserito non nel range 1:4 e fa un
> exit sub se l'utente clicca su annulla. Sono felicissima del risultato.
> Grazie mille ragazzi siete stati ancora utilissimi e gentilissimi!
ottimo allora!
brava Patty!
saluti
r
eliano
Mi associo ovviamente.
[Sperando di non sconvolgere Fratello Mauro], Roby, in fondo, fare
felice le donne è una delle nostre missioni. :-))
Ciao
Eliano
r
>
> - Mostra testo citato -
fai il bravo ... Yoda :-)
puf puf puf
r
https://sites.google.com/site/e90e50/documento-plinius/guerre-stellari#TOC-Yoda
eliano
>
> - Mostra testo citato -
Beh, in fondo devo "solo" fare un po' di lifting (si fa per dire).:-))
Patty
Ciao ragazzi,
anno nuovo problema nuovo... sto tentando di fare in modo che la macro
peschi i dati del vlookup in una cartella di lavoro che sia diversa da
quella in cui mi trovo (mi trovo in ordine.xls e voglio pescare da
articoli.xls ad esempio)
se io sostituisco nel codice
Set sh1 = ThisWorkbook.Sheets(giornoSett)
con
Set sh1 = Workbooks("C:\articoli.xls").Sheets(giornoSett)
excel mi restituisce errore.
E' necessario a tutti i costi usare prima Workbooks.Open? E se volessi
fare in modo che il nuovo file si aprisse senza farsi vedere?
Sto provando in diversi modi, ma non riesco a risolvere la questione... :(
r
>
> - Mostra testo citato -
è possible utilizzare una connessione (ADO) ma la strada è pù
complicata, io seguirei quella di aprire il file ... qui sotto due
semplici esempi, in test viene utilizzata un nuovo oggetto
application, reso non visibile e li viene aperto il file ... nella
seconda semplicemente viene usato Application.ScreenUpdating per non
mostrare il file durante l'esecuzione ... negli esempi ho creato il
file mesi con la tabella da 1 a 12 in A1:A12 e i loro nomi in B1:B12:
Sub test()
Dim wb As Workbook
Dim rng As Excel.Range
Dim app As New Application
app.Visible = False
Set wb = app.Workbooks.Open("D:\Documents and Settings\customer\Desktop
\mesi.xls", , True)
Set rng = wb.ActiveSheet.[a1:b12]
MsgBox Application.WorksheetFunction.VLookup(1, rng, 2, 0)
wb.Close False
app.Quit
End Sub
Sub test2()
Dim wb As Workbook
Dim rng As Excel.Range
Application.ScreenUpdating = False
Set wb = Workbooks.Open("D:\Documents and Settings\customer\Desktop
\mesi.xls", , True)
Set rng = wb.ActiveSheet.[a1:b12]
MsgBox Application.WorksheetFunction.VLookup(1, rng, 2, 0)
wb.Close False
Application.ScreenUpdating = True
End Sub
saluti
r
Patty
Il 04/01/11 16.10, r ha scritto:
> č possible utilizzare una connessione (ADO) ma la strada č pů
r
> Ancora grazie mille r, ancora problema risolto!
>
> Il 04/01/11 16.10, r ha scritto:
>
> > complicata, io seguirei quella di aprire il file ... qui sotto due
> > semplici esempi, in test viene utilizzata un nuovo oggetto
bene!
ciao
r