Patroon met gelijkblijvende factoren

[DeToekomst2]

Goedemorgen,


Ik ben op het moment bezig met het bouwen van een excel bestand waar automatisch tabellen ingevuld kunnen worden. Dit wordt gedaan met een als-functie. Als het ingevulde artikelnumer (in de invullijst -zie voorbeeldbestand-) overeenkomt met een van de artikels in de tabel worden de gegevens over genomen. Als dat niet zo is blijft de tabel 'zichzelf'.
Nu staat in de tabel een patroon aan formules die in elke cel ingevuld moet worden. Nu ben ik bezig met dit met de hand te doen maar het lijkt me toch een stuk sneller als hier een bepaalde opdracht voor is binnen excel.

De vraag is nu of ik de tabel heel snel kan invullen en het juiste patroon in de formules kan behouden. Bepaalde cel code's moeten veranderen en andere moeten hetzelfde blijven. Dit patroon kun je uit het voorbeeldbestand halen.

Ik hoop dat de vraag duidelijk is


Groeten.

Bekijk bijlage HelpMij Voorbeeldtabel.xlsx

 

[AlexCEL]

Een voorzetje...

Ik zou geen ALS functie gebruiken, daar kun je moeilijk mee zoeken. VERT.ZOEKEN is wel geschikt. Tenminste, dit concludeer ik n.a.v. je vraag.

Gebruik deze in C4:

=ALS.FOUT(VERT.ZOEKEN($B4;$K$4:$Q$20;KOLOM()-1;0);"-")

Voorbeeldje: Bekijk bijlage HelpMij Voorbeeldtabel (AC).xlsx

 

[DeToekomst2]

Ziet er goet uit

Bedankt voor de reactie,


Nu is het met die formule niet zo dat de ingevoerde gegevens onthouden worden in de tabel, zoals wel gebeurd met de als-functie. Die blijft namelijk zichzelf als =fout waarde.
Als de formule
=ALS.FOUT(VERT.ZOEKEN($B4;$K$4:$Q$20;KOLOM()-1;0);"-")
aangepast kan worden dat de gegevens niet worden verwijdert neem ik 'm uiteraard over. Ik ben zelf helaas nog niet zo'n pro met excel...


Alvast bedankt voor de moeite!

 

[AlexCEL]

In standaard Excel kan je niet én een formule én een waarde in 1 cel hebben. Het is of het een, of het ander...

Je krijgt al kringverwijzingen (=foutmelding) in je voorbeeld... dat komt juist omdat de ALS functie naar de cel verwijst waarin deze staat.

 

[DeToekomst2]

Klopt, en dat is eigenlijk precies wat ik wil. Op die manier kan je heel snel de tabel invullen uit één lijst. Ik voeg dan een macro toe die uit een andere lijst met toe te voegen artikelen één voor één de artikelen in de invoer-lijst zet en deze er vervolgens meteen weer uit haalt. Dan worden de gegevens gekopieerd naar de definitieve tabel en kunnen de gegevens van het volgende artikel worden ingevoerd.

Dus ik denk dat er daar voor weinig anders op zit dan een kringverwijzing. Maar zelf heb je geen goed idee om de formule die ik heb opgesteld snel in te voeren? Dit handwerk komt me ondertussen wel mijn neus uit...


Groeten.

 

[AlexCEL]

Als ik het goed begrijp wil je tabel A aanvullen met gegevens uit tabel B als er niets ingevuld is in tabel A.

Oplossing ligt in VBA denk ik (een macro).

Weer een uitdaging voor de specialisten op dit forum (en daar reken ik mezelf niet toe).

 

[DeToekomst2]

Dan wacht ik met spanning af op het advies van de experts!

In de bijlage heb ik het voorbeeld uitgebreid naar wat ik nu op grotere schaal aan het bouwen ben (+/- 20.000 artikelen...). Hierin zit ook het invul verhaal in.
Het doel van dit bestand is om er voor te zorgen dat er niet meer in de grote moederlijst gezocht hoeft te worden naar het artikel wat aangepast moet worden én artikelen die allemaal dezelfde beoordeling krijgen kunnen met een aantal keer klikken achter elkaar ingevuld worden. Dit is allemaal voor het gemak van de administrator.
Het niet hoeven zoeken had ik op kunnen lossen met een filter maar de artikelen worden verspreid over meerdere tabbladen wat de zaak iets lastiger maakt.

Bekijk bijlage HelpMij Voorbeeldtabel Macro.xlsm

 

[Peter B]

Ik ben zeker ook geen expert, maar ik had dit in elkaar gedraaid. Op dit moment wordt de macro getriggered als alle cellen in de range K4:Q4 zijn gevuld.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long

    If Target.Row = 4 And Target.Column > 10 And Target.Column < 18 Then
        If Range("K4:Q4").Cells.SpecialCells(xlCellTypeConstants).Count = 7 Then
            lastRow = Cells(Rows.Count, "B").End(xlUp).Row
            Range("K4:Q4").Copy
            Range("B" & lastRow + 1).PasteSpecial xlPasteValues
            Application.EnableEvents = False
            Range("K4:Q4").ClearContents
            Application.EnableEvents = True
            Range("K4").Select
        End If
    End If
End Sub

 

[DeToekomst2]

Ziet er indrukwekkend uit, alleen ben ik nog een broekie in VBA.
Is er een goede site of een goed filmpje wat mij uitlegt hoe ik jouw VBA-code moet toepassen in mijn excel bestand?

In ieder geval bedankt!

 

[gast0660]

Als je een representatief bestandje post wil ik wel een oplossing bedenken voor

Dit handwerk komt me ondertussen wel mijn neus uit...

Waarom zeg ik representatief, je spreekt over

maar de artikelen worden verspreid over meerdere tabbladen

Maar in uw vb worden zijn de andere tabbladen leeg.Waar komen de gegevens vandaan? Wat moet er gebeuren met een lijst die afgewerkt is en je een nieuwe lijst wilt beginnen?

 

[VenA]

Maak gebruik van echte tabellen (ListObjects)
Voor het zoeken van een artikel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f as range
If Target.Address = "$K$4" Then
    Application.EnableEvents = False
    For Each sh In Sheets
        For Each lo In sh.ListObjects
            Set f = sh.ListObjects(lo.Name).DataBodyRange.Find(Target, , xlValues, xlWhole)
            If Not f Is Nothing Then
                [L4:Q4] = sh.Cells(f.Row, f.Column).Offset(, 1).Resize(, 6).Value
                b = True
                Exit For
            End If
        Next lo
        If b Then Exit For
    Next sh
    If f Is Nothing Then MsgBox "Artikel niet gevonden", vbCritical
    Application.EnableEvents = True
End If
End Sub

Voor het opslaan

Sub VenA()
Dim f As Range, lo, b As Boolean
With Blad1
    For Each sh In Sheets
        For Each lo In sh.ListObjects
            Set f = sh.ListObjects(lo.Name).DataBodyRange.Columns(1).Find(.[K4], , xlValues, xlWhole)
            If Not f Is Nothing Then
                sh.Cells(f.Row, f.Column).Resize(, 7) = .[K4:Q4].Value
                .[O7] = .[K4]
                .[K4:Q4].ClearContents
                b = True
                Exit For
            End If
        Next lo
        If b Then Exit For
    Next sh
    If f Is Nothing Then MsgBox "Artikel niet gevonden", vbCritical
End With
End Sub

Het wordt een stuk eenvoudiger als je alle artikelen in één tabel zet en dan gebruik gaat maken van een Userform.

 

[DeToekomst2]

Mooie code!

Ik probeer 'm te implementeren in mijn bestand, alleen dat gaat nog niet zo best. Ik werk in mijn bestand met een grote moederlijst. Deze lijst wordt verborgen en dit is ook de enige lijst die de waardes moet overnemen uit de invoertabel. Dit overnemen krijg ik echter niet voor elkaar. Ik heb de code bekeken en uitgevogeld welke cellen gebruikt worden in de code. Deze heb ik aangepast naar het nieuwe bestand alleen dan werkt het nog niet. Heeft dit te maken met het gebruik van meerdere tabbladen of het gebruik van 'neppe' tabellen (ik gebruik geen tabellen zoals je die in excel kunt invoegen)?

Ik heb een tijdje stilgezeten met dit bestand en pak het nu weer op, vandaar de late reactie.


Gr.

 

[HSV]

De codes zoals geplaatst door @VenA zijn voor echte tabellen (ListObjects) die je kunt invoegen via Menu Invoegen.

 

[DeToekomst2]

Ik krijg het nog steeds niet voor elkaar. Ik van de moederlijst een tabel gemaakt en de codes aangepast. Het werkt alleen nog steeds niet.
In de bijlage heb ik snel even een voorbeeldje gemaakt van hoe het er uit ziet in mijn bestand.
De moederlijst wordt verborgen. Wat mij betreft mag er ook gewerkt worden zonder moederlijst, maar dat krijg ik al helemaal niet voor elkaar...

Hoe moet ik de code aanpassen zodat het werkt voor mijn bestand?

 

[VenA]

Er staat geen code in jouw bestand. Misschien dat het daarom niet werkt! Heb je nu wel of geen moederlijst? Waarom staan de gegevens in de verschillende tabjes niet op dezefde plek? Waarom zijn het geen tabellen? Waarom heb je het bericht van 11:09 uur verwijderd en plaats je het zelfde bericht en bestand nu opnieuw?

Volgens mij hoef je maar een paar dingen aan te passen om het werkend te krijgen. Ook even onder

Private Sub Worksheet_Change(ByVal Target As Range)

f als range declareren.

 

[DeToekomst2]

Er staat inderdaad geen code in mijn bestand, het gaat met name om de lay-out en hoe ik daar de code voor aan moet passen.

Ik heb in mijn bestand wél een moederlijst, al zou ik daar ook graag van af willen. Ik heb dat origineel gedaan zodat ik niet hoef te zoeken op meerdere werkbladen. Zoals je in het voorbeeldbestand 'TabelInvoer' kunt zien nemen alle kleinere tabellen (a, b en c) hun gegevens over van de moederlijst. In het voorbeeldbestand wat jij (VenA) mij hebt gegeven worden ook andere tabellen op andere tabbladen invullen en dat is wat ik graag wil.

De tabellen in de verschillende werkbladen moeten wel op dezelfde plek staan, dat is mijn fout. Heb daar niet op gelet tijdens het maken van het voorbeeldje.

Die gegevens op de verschillende tabjes zijn geen tabellen omdat dit een voorbeeld is van hoe het bestand er nu uit ziet. In dat bestand zijn geen tabellen gebruikt. Ik heb van de moederlijst wel een tabel gemaakt omdat mij is ingefluisterd (door HSV) dat de codes alleen voor echte tabellen werkt. Ik snap de verwarring, waarvoor excuses.

Het bericht was verwijdert omdat ik dacht dat ik een oplossing had gevonden, maar dat bleek niet waar te zijn en heb ik de vraag gekopieerd en opnieuw geplaatst.

Verder, wat betekent f als range declareren?


Aanvulling:
Ik heb het op eigen houtje opgelost
Het werkt super en ik ben ook van de moederlijst af. Allen bedankt voor jullie hulp!