VLOOKUP cu VBA
VLOOKUP cu VBA
Buna ziua.
Fie fisierul de exemplu.
In foaia 1 sunt 2 mici tabele cu rol de nomenclator. In foaia 2 este un tabel unde se opereaza. Ideea este ca userul sa selecteze via dropdown o valoare din campul nume (tabel2), iar via VBA sa returneze valoarea corespondenta in campul activitate conform nomenclatorului. Evident intrebarea este: dar de ce cu VBA si nu cu formula? Pentru ca in ~20 % din cazuri (inregistrari) userul doreste sa schimbe activitatea implicita (definita prin nomenclator), si sa aloce o alta, dar cert o activitate care se regaseste in nomenclator. De aceea campul de activitate l-am pus cu dropdownuri goale. Pentru ca intr-o celula validata ca lista indiferent scrii "de mana" sau cu VBA, acest lucru este posibil atat timp cat valoarea este regasita in lista de validare.
Acum nu zic ca solutia gasita de mine la acasta situatie este cea mai buna; sunt deschis la sugestii, dar asta mi-a venit mie.
Multumesc.
Fie fisierul de exemplu.
In foaia 1 sunt 2 mici tabele cu rol de nomenclator. In foaia 2 este un tabel unde se opereaza. Ideea este ca userul sa selecteze via dropdown o valoare din campul nume (tabel2), iar via VBA sa returneze valoarea corespondenta in campul activitate conform nomenclatorului. Evident intrebarea este: dar de ce cu VBA si nu cu formula? Pentru ca in ~20 % din cazuri (inregistrari) userul doreste sa schimbe activitatea implicita (definita prin nomenclator), si sa aloce o alta, dar cert o activitate care se regaseste in nomenclator. De aceea campul de activitate l-am pus cu dropdownuri goale. Pentru ca intr-o celula validata ca lista indiferent scrii "de mana" sau cu VBA, acest lucru este posibil atat timp cat valoarea este regasita in lista de validare.
Acum nu zic ca solutia gasita de mine la acasta situatie este cea mai buna; sunt deschis la sugestii, dar asta mi-a venit mie.
Multumesc.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: VLOOKUP cu VBA
Buna ziua
Atasat aveti spre testare o propunere folosind macro
Obiectiv: inserea in celula alaturata numelui a informatiei aferente extrasa dintr-un tabel de corespondenta
Premise: nu se schimba denumirile de foi sau tabele sau se va modifica in cod
tabelul destinatie va incepe de la A1 si nu va fi mutat in cadrul foii.
Am folosit urmatorul cod:
Pentru testare: deschideti fisierul atasat, activati macro/continutul; In foaie 2 alegeti un nume din lista si, lasand selectata celula respectiva, apasati "butonul" albastru.
IP
Atasat aveti spre testare o propunere folosind macro
Obiectiv: inserea in celula alaturata numelui a informatiei aferente extrasa dintr-un tabel de corespondenta
Premise: nu se schimba denumirile de foi sau tabele sau se va modifica in cod
tabelul destinatie va incepe de la A1 si nu va fi mutat in cadrul foii.
Am folosit urmatorul cod:
Cod: Selectaţi tot
Sub Completare()
'IPP - 25.08.2018
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If ActiveCell.Column > 1 Or ActiveCell = "" Then
MsgBox "Trebuie sa selectati o celula de pe coloana A care are completat un nume"
Exit Sub
End If
ActiveCell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(ActiveCell, Sheets("Foaie1").ListObjects("nomenclator").Range, 2, False)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Re: VLOOKUP cu VBA
Buna ziua,
Nu ar fi mai simplu sa pui o formula pentru activitatea implicita si sa blochezi accesul la acea zona? (range, coloana ..etc)
Cine il opreste sa modifice activitatea implicita dupa rularea codului VBA? cu VBA tot ar trebui totusi sa blochezi accesul la acea zona si sa pui parola.Evident intrebarea este: dar de ce cu VBA si nu cu formula? Pentru ca in ~20 % din cazuri (inregistrari) userul doreste sa schimbe activitatea implicita (definita prin nomenclator), si sa aloce o alta
Nu ar fi mai simplu sa pui o formula pentru activitatea implicita si sa blochezi accesul la acea zona? (range, coloana ..etc)
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
Re: VLOOKUP cu VBA
Buna ziua
@H2SO4
Eu am inteles urmatoarele: exista nevoia de completare "automata" cu informatie pentru rapiditate. Daca in proportie de 80% completare se face cf. cu tabelul sursa si reprezinta "regula" si in 20% dintre cazuri, acea autocompletare nu este corecta, reprezinta exceptiile si informatia va trebui modificata manual in functie de situatia reala din acel moment, la un volum mare de informatii se justifica existenta unui astfel de macro.
Nu am ramas cu impresia ca se doreste blocarea optiunilor utilizatorilor.
IP
@H2SO4
Eu am inteles urmatoarele: exista nevoia de completare "automata" cu informatie pentru rapiditate. Daca in proportie de 80% completare se face cf. cu tabelul sursa si reprezinta "regula" si in 20% dintre cazuri, acea autocompletare nu este corecta, reprezinta exceptiile si informatia va trebui modificata manual in functie de situatia reala din acel moment, la un volum mare de informatii se justifica existenta unui astfel de macro.
Nu am ramas cu impresia ca se doreste blocarea optiunilor utilizatorilor.
IP
Re: VLOOKUP cu VBA
Buna ziua,
@IPP
As fi inteles daca se completau, sa zicem 500 de nume si indiferent ce alegeau ei pentru activitate, codul VBA rescria cu informatia corecta. Asa unde este rapiditatea?
Raman la parerea mea, ca este mai usor, ca fiecare sa isi scrie numele si automat (cu formule) sa apara activitatea aferenta acelui nume, (evident coloana cu activitati sa fie blocata)
@IPP
Care rapiditate? caci codul nu te lasa sa selectezi decat o singura celula in col A si apoi clic ptr rulare codEu am inteles urmatoarele: exista nevoia de completare "automata" cu informatie pentru rapiditate.
As fi inteles daca se completau, sa zicem 500 de nume si indiferent ce alegeau ei pentru activitate, codul VBA rescria cu informatia corecta. Asa unde este rapiditatea?
La un volum mare de date creste timpul de modificare deoarece se face celula cu celula. Se verifica userul care a pus alta activitate si apoi clic -rulare cod VBA- pentru modificare...si asa adio rapiditate...si in 20% dintre cazuri, acea autocompletare nu este corecta, reprezinta exceptiile si informatia va trebui modificata manual in functie de situatia reala din acel moment, la un volum mare de informatii se justifica existenta unui astfel de macro.
Raman la parerea mea, ca este mai usor, ca fiecare sa isi scrie numele si automat (cu formule) sa apara activitatea aferenta acelui nume, (evident coloana cu activitati sa fie blocata)
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
Re: VLOOKUP cu VBA
Eu vedeam procesul cam de felul urmator:
informatia se completeaza pe rand, de una sau mai multe persoane. Dupa alegerea numelui se apasa butonul si se autocompleteaza cu informatia predefinita. Eu cred ca e sensibil mai rapid sa apesi un buton decat sa cauti intr-o lista de datavalidation daca vorbim de multe item-uri.
Vizavi de partea regula vs. exceptie. Sa spunem ca persoana X e paznic, cf. fisei postului dar are si carnet de sofer. E foarte posibil ca in 99% din cazuri in dreptul numelui sau sa fie adusa o activitate legata de fisa postului si o singura data sa fie nevoie de alta activitate (ex. trebuie sa transporte ceva cu masina), caz in care se alege altceva din lista.
In fine, am facut niste presupuneri legate de cerinta lui Rambo, ma opresc aici. In final conteaza daca il ajuta sau nu propunerea mea sau ideea dvs.
informatia se completeaza pe rand, de una sau mai multe persoane. Dupa alegerea numelui se apasa butonul si se autocompleteaza cu informatia predefinita. Eu cred ca e sensibil mai rapid sa apesi un buton decat sa cauti intr-o lista de datavalidation daca vorbim de multe item-uri.
Vizavi de partea regula vs. exceptie. Sa spunem ca persoana X e paznic, cf. fisei postului dar are si carnet de sofer. E foarte posibil ca in 99% din cazuri in dreptul numelui sau sa fie adusa o activitate legata de fisa postului si o singura data sa fie nevoie de alta activitate (ex. trebuie sa transporte ceva cu masina), caz in care se alege altceva din lista.
In fine, am facut niste presupuneri legate de cerinta lui Rambo, ma opresc aici. In final conteaza daca il ajuta sau nu propunerea mea sau ideea dvs.
Re: VLOOKUP cu VBA
Buna,
Eu am interpretat (putin) diferit textul mesajului fata de colegii mei, prin urmare va ofer si eu o solutie.
Codul functioneaza in felul urmator:
- daca se introduce (selecteaza) un nume in coloana 'NUME' din foaia2, se introduce automat in coloana 'ACTIVITATE' valoarea corespunzatoare numelui selectat, preluata din nomenclatorul cu persoane.
- utilizatorul este liber ulterior sa selecteze orice alta activitate.
- daca utilizatorul a selectat un nume in unica linie libera din tabel, se adauga automat o linie noua pentru urmatoarea inregistrare
In cazul in care se revine la o linie completata anterior, codul modifica valoarea din coloana'ACTIVITATE' si introduce din nou valoarea corespunzatoare (implicita)
Daca doriti ca la o eventuala revenire, daca in coloana 'ACTIVITATE' exista deja o valoare selectata, sa nu o mai modifice, gasiti in cod doua linii (IF - End If) marcate ca comentariu. Anulati apostroful din dreptul celor doua instructiuni si din acel moment, valoarea odata selectata in coloana 'Activitate' nu va mai fi modificata, chiar daca se va modifica ulterior numele.
Codul trebuie introdus in modulul foii in care se face introducerea datelor,
Modificati daca este cazul denumirile constantelor definite la inceput corespunzator celor din fisierul dv.
Eu am interpretat (putin) diferit textul mesajului fata de colegii mei, prin urmare va ofer si eu o solutie.
Codul functioneaza in felul urmator:
- daca se introduce (selecteaza) un nume in coloana 'NUME' din foaia2, se introduce automat in coloana 'ACTIVITATE' valoarea corespunzatoare numelui selectat, preluata din nomenclatorul cu persoane.
- utilizatorul este liber ulterior sa selecteze orice alta activitate.
- daca utilizatorul a selectat un nume in unica linie libera din tabel, se adauga automat o linie noua pentru urmatoarea inregistrare
In cazul in care se revine la o linie completata anterior, codul modifica valoarea din coloana'ACTIVITATE' si introduce din nou valoarea corespunzatoare (implicita)
Daca doriti ca la o eventuala revenire, daca in coloana 'ACTIVITATE' exista deja o valoare selectata, sa nu o mai modifice, gasiti in cod doua linii (IF - End If) marcate ca comentariu. Anulati apostroful din dreptul celor doua instructiuni si din acel moment, valoarea odata selectata in coloana 'Activitate' nu va mai fi modificata, chiar daca se va modifica ulterior numele.
Codul trebuie introdus in modulul foii in care se face introducerea datelor,
Modificati daca este cazul denumirile constantelor definite la inceput corespunzator celor din fisierul dv.
Cod: Selectaţi tot
Option Explicit
'------------------------------------------------------------------------------------------------
' modificati denumirile corespunzator fisierului dv
Const shListe As String = "Foaie1" 'denumirea foii in care se afla tabelele sursa (Nomenclatoare)
Const tblPers As String = "nomenclator" 'denumirea tabelului din shListe ce contine numele persoanelor
Const tblInput As String = "Tabel2" 'denumirea tabelului in care se introduc datele
Const colNumeL As String = "nume" 'denumirea colanei cu numele persoanelor din tblPers
Const colActDefaultL As String = "activitate" 'denumirea coloanei cu activitatea implicita din tblPers
Const colNumeI As String = "NUME" 'denumirea colanei cu numele persoanelor din tblInput
Const colActI As String = "ACTIVITATE" 'denumirea coloanei cu activitati din tblInput
' ----------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Dim rSel As Range, r As Range
Dim offs As Long
Dim i As Long
Set rSel = Intersect(Target, Range(tblInput & "[" & colNumeI & "]"))
If Not rSel Is Nothing Then
Set tbl = ListObjects(tblInput)
If Application.WorksheetFunction.CountA(Range(tblInput & "[" & colNumeI & "]")) = tbl.ListRows.Count Then tbl.ListRows.Add
offs = tbl.ListColumns(colActI).Range.Column - tbl.ListColumns(colNumeI).Range.Column
Set tbl = ThisWorkbook.Sheets(shListe).ListObjects(tblPers)
For Each r In rSel
' If Len(r.Offset(, offs).Value) = 0 Then
For i = 1 To tbl.ListRows.Count
If tbl.ListColumns(colNumeL).DataBodyRange(i).Value = r.Value Then
r.Offset(, offs).Value = tbl.ListColumns(colActDefaultL).DataBodyRange(i).Value
Exit For
End If
Next i
' End If
Next r
Set tbl = Nothing
End If
Set rSel = Nothing
End Sub
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.
Re: VLOOKUP cu VBA
Atat solutiile oferite de IPP si TudyBTH fac exact ceea ce trebuie; in moduri usor diferite. Interesanta adaugarea automata a unei noi linii necompletate, si, da, voi opta pentru varianta ca odata aleasa o activitate aceasta sa nu se mai modifice la o eventiala revenire. Regret ca nu m-am facut inteles pentru toata lumea. Multumesc foarte mult. Raman consternat de capabilitatile colegilor forumisti in materie de VBA!
Re: VLOOKUP cu VBA
Ar fi mai rapid dar...pentru ce mai este necesara folosirea datavalidation daca oricum fiecare user are doar o singura activitate ? Poate un DV particularizat care te-ar lasa sa alegi doar acivitatea corecta.IPP scrie:Eu vedeam procesul cam de felul urmator:
informatia se completeaza pe rand, de una sau mai multe persoane. Dupa alegerea numelui se apasa butonul si se autocompleteaza cu informatia predefinita. Eu cred ca e sensibil mai rapid sa apesi un buton decat sa cauti intr-o lista de datavalidation daca vorbim de multe item-uri.
Daca "X e paznic, cf. fisei postului dar are si carnet de sofer", eu l-as diferentia prin ceva (o litera/semn in plus sau orice altceva) si in felul acesta ar avea doar o singura activitate, deci iar ar fi inutil DVVizavi de partea regula vs. exceptie. Sa spunem ca persoana X e paznic, cf. fisei postului dar are si carnet de sofer. E foarte posibil ca in 99% din cazuri in dreptul numelui sau sa fie adusa o activitate legata de fisa postului si o singura data sa fie nevoie de alta activitate (ex. trebuie sa transporte ceva cu masina), caz in care se alege altceva din lista.
Eu raman la convingerea ca, sunt foarte rare cazurile (ceva de genul 1 la un milion) in care un cod VBA (si nu intru in amanunte despre coduri care folosesc array, dictionare cu calculare direct in memorie) este mai rapid/bun decat o functie nativa a excelului.
Iar daca ceva se poate rezolva cu formule, atunci eu cred ca este vorba de comoditate sau altceva din partea celui care doreste folosirea VBA cand are la dispozitie formule (si stie sa le foloseasca). Nu vreau sa ofensez pe nimeni dar asta este parerea mea.
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
Re: VLOOKUP cu VBA
Fiecare user are o singura activitate hai sa-i zicem de baza, care este nomenclaturizata. Teoretic, daca totul merge ca pe roate (ca sa zic asa), evident ca folosirea VBA-ului in loc de VLOOKUP este fix anus contra naturii. Dar daca userul X are in nomenclator activitatea A, se imbolnaveste si isi ia concediu medical, atunci trebuie inlocuit cu un user disponibil, sa zicem userul Y care are in nomenclator activitatea B, dar in cazul asta va face activitatea A. Cum documentez in cazul asta fara sa distrug formula VLOOKUP? Asa cum am spus, solutia cu VBA este la ce m-am gandit eu, nu ca ar fii cea mai buna alegere. Avand in vedere ca in tabelul original userul are mai multe campuri de completat, am zis ca orice ajutor ar fii de folos. Evident, puteam sa-mi bag cracul in cel care va opera pe fisier, si sa-i trantesc un list DV la nume, un list DV la activitate etc, etc si facea la clickuri + scroll in lista de ma blestema 3 generatii de aici in colo. Cerinta celui care mi-a dat sarcina sa concep workbookul a fost destul de clara: operatorul sa aleaga un nume, iar activitatea (and others) sa fie returnata automat din nomenclator cu posibilitatea modificarii, la nevoie, manual a valorii returnate. Daca s-ar putea fara VBA ar fii excelent, evident. Daca nu, o sa incerc sa inteleg & modific codul propus in solutiile oferite pentru a-l adapta la fisierul original. Iar daca nu reusesc, o sa rog (iar) pe cineva sa ma ajute.