VLOOKUP cu VBA

[RAMBO]

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.

[IPP]

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:

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

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

[H2SO4]

Buna ziua,

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

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.

Nu ar fi mai simplu sa pui o formula pentru activitatea implicita si sa blochezi accesul la acea zona? (range, coloana ..etc)

[IPP]

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]

Buna ziua,

@IPP

Eu am inteles urmatoarele: exista nevoia de completare "automata" cu informatie pentru rapiditate.

Care rapiditate? caci codul nu te lasa sa selectezi decat o singura celula in col A si apoi clic ptr rulare cod
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?

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.

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...

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]

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.

[TudyBTH]

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.

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

[RAMBO]

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!

[H2SO4]

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.

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.

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.

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 DV

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.

[RAMBO]

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.