Unieke waarden weergeven

[TTLxITB]

Beste mensen,

Ik probeer uit een matrix met relatienummers een overzicht te maken van de individuele relatienummers die hier in voor komen.
Dit is eigenlijk dezelfde vraag als die in deze thread is gesteld: http://www.helpmij.nl/forum/showthread.php/791823-Unieke-waarden-uit-matrix-in-kolom-weergeven, alleen heb ik een Nederlandstalige versie van Excel en totaal geen idee wat ik moet invoeren. Ik heb niet eerder met matrices gewerkt, dus het misschien is het antwoord heel voor de hand liggend, maar alle hulp is welkom.

Ik heb in de bijlage het bestand toegevoegd, de waarden moeten uit alle maanden gefilterd worden, zodat dit straks automatisch gebeurd als er waarden bij bvb november en december worden ingevoerd.

Alvast bedankt!

 

[arrie23]

Engelse of Nederlandse versie maakt voor VBA niet uit. Klik op Alt+F11, dan kom je in de VBA-editor. Klik linksboven met de rechtermuisknop op Blad1 en kies voor Invoegen - Module.

Plak onderstaande code (rechtstreeks uit de thread waar je naar verwijst gehaald) in het witte vak rechts (Eventueel "Voorbeeld" in de code vervangen door de echte naam van het werkblad waarop je matrix staat):

Sub Overzicht()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    For Each cl In Sheets("Voorbeeld").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(2)
        If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
    Next cl
    
    With Sheets("Blad2")
        .Columns(1).ClearContents
        .Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
    End With
    Application.ScreenUpdating = True
    
End Sub

Keer via het Excel-icoontje weer terug naar je 'gewone' Excel-bestand. Klik op Alt+F8 en klik op 'Overzicht - Uitvoeren'
In tabblad 2 worden nu alle unieke waarden geplaatst.

Let op: sla het bestand op als .xlsm (bestand met macro's) ipv .xlsx anders ben je de macro weer kwijt

 

[TTLxITB]

Beste arrie23 en andere helpers,

Allereerst bedankt voor je reactie!
Ik heb de stappen zoals je aangeeft uitgevoerd, maar loopt alsnog tegen een probleem aan.

Wanneer ik in het voorbeeld bestand werk, gaat alles prima. Echter in het originele bestand krijg ik de volgende melding:
'Fout 1004 tijdens uitvoering: Er zijn geen cellen gevonden'.
Wanneer ik vervolgens naar de foutopsporing ga wordt onderstaande regel gearceerd.
For Each cl In Sheets("Relatieoverzicht-b").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(2)

Ik heb geprobeerd uit te voeren met de juiste namen van de werkbladen (in het originele bestand heet het werkblad 'Voorbeeld' -> 'Relatieoverzicht-b' (dit is blad1 in het bestand) en zouden de unieke waarden moeten worden weergegeven op het werkblad 'Relatieoverzicht' (blad2)). Ook heb ik het geprobeerd met de namen zoals ze in het voorbeeld staan in het originele bestand te werken (Dus naam werkblad gewijzigd), maar in beide gevallen wordt de foutmelding gegeven. Kan dit komen omdat de waardes in het originele bestand verkregen worden door verwijzingen naar een ander excel bestand? Of klopt er iets niet in de codering zoals ik deze invoer?

Wederom alvast bedankt!

Ps. de code zoals ik deze heb ingevoerd is:

Sub Overzicht()

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For Each cl In Sheets("Relatieoverzicht-b").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(2)
If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
Next cl

With Sheets("Relatieoverzicht")
.Columns(1).ClearContents
.Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
End With
Application.ScreenUpdating = True

End Sub

 

[arrie23]

Ik denk dat het inderdaad te maken heeft met de verwijzingen. Kopieer als test de waarden van 'Relatieoverzicht-b' eens en plak ze als waarden. Probeer dan nogmaals de macro te runnen

 

[TTLxITB]

Dag arrie,

Bedankt voor je snelle reactie!
De matrix gekopieerd en als waarde in een nieuw werkblad geplakt, nog een keer bovenstaande uitgevoerd, en nu doet ie het inderdaad wel gewoon. Dus toch de verwijzingen.
Het ziet er dus naar uit dat ik dit dus niet automatisch kan laten verlopen, of weet je hier toevallig een trucje voor?

Gr.

 

[arrie23]

Probeer deze eens

Sub Overzicht2()

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For Each cl In Sheets("Relatieoverzicht-b").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(xlFormulas)
If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
Next cl

With Sheets("Relatieoverzicht")
.Columns(1).ClearContents
.Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
End With
Application.ScreenUpdating = True

End Sub

 

[TTLxITB]

Werkt perfect, hardstikke bedankt!

Nu wil ik alleen nog een soort update knop maken (die eigenlijk alt+f8 overneemt) zodat wanneer het excel bestand waarnaar wordt verwezen wordt gewijzigd het werkblad met de unieke waarden automatisch wordt aangepast (zodat ook collegas het bestand probleemloos kunnen gebruiken). Ik kan me uit mijn studietijd herinneren dat er een soort 'filmpje' gemaakt kan worden waarbij excel je stappen exact uitvoert wanneer je op de knop zou drukken (hoe dit precies zit zoek ik zelf wel uit). Mijn laatste vraag is dan alleen nog: waar is alt+f8 de shortcut voor? (Dit zodat ik het 'filmpje' kan maken)

Gr.

 

[spaarie]

Of met een formule, hoef je ook geen 'update' knop te gebruiken...

 

[boomen]

Ja nu kan ik helpen!
ALT+F8 zorgt ervoor dat je je macro menu opent.

Beeld>Macro's>Macro's weergeven

 

[TTLxITB]

Dag Spaarie,

Deze formule had ik geprobeerd, probleem is alleen dat de unieke waarde uit meerdere kolommen en rijen moet worden geselecteerd, dat is volgens mij met die formule niet mogelijk (het lukt mij iig niet)

gr.

 

[TTLxITB]

Bedankt Boomen!
problem solved volgens mij

 

[TTLxITB]

Bij mij staat het onder Beeld-Macro's-Macro's weergeven. Heb Excel2010 en Windows7, misschien dat daar het verschil zit?

Nogmaals bedankt voor de behulpzame reactie overigens!

 

[arrie23]

Om bij elke wijziging de macro volautomatisch opnieuw te laten draaien kun je ipv een knop gebruik maken van onderstaande code.
Ga in je Excel-bestand onderin op de naam van het tabblad staan ("Relatieoverzicht-b"). Rechter muisknop: Programmacode weergeven. Hierin onderstaande code plakken. Als er iets wijzigt aan het tabblad ("Relatieoverzicht-b")
draait de macro als het goed is automatisch opnieuw. Niet getest met de verwijzingen, in je oorspronkelijke voorbeeldbestand met de waarden werkt het in ieder geval.

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For Each cl In Sheets("Relatieoverzicht-b").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(xlFormulas)
If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
Next cl

With Sheets("Relatieoverzicht")
.Columns(1).ClearContents
.Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
End With
Application.ScreenUpdating = True    
End Sub

 

[TTLxITB]

Dag arrie,

Was even een dagje buiten de deur, dus vandaar de late reactie.
Ik heb bovenstaande handelingen uitgevoerd, maar de automatische update wordt niet uitgevoerd.
De code heb ik onder de code die je eerder gegeven had geplakt, en daarna ook alleen die code geprobeerd (maar dan was er helemaal geen macro meer zichtbaar).
In beide gevallen werkte het niet. Waar kan dit aan liggen?

Mvg

 

[TTLxITB]

Onderstaande code staat er nu in trouwens:
Sub Overzicht2()

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For Each cl In Sheets("Relatieoverzicht-b").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(xlFormulas)
If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
Next cl

With Sheets("Relatieoverzicht")
.Columns(1).ClearContents
.Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
End With
Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For Each cl In Sheets("Relatieoverzicht-b ").Cells(1).CurrentRegion.Offset(1, 1).SpecialCells(xlFormulas)
If Not dic.exists(cl.Value) Then dic.Add cl.Value, Nothing
Next cl

With Sheets("Relatieoverzicht ")
.Columns(1).ClearContents
.Cells(1).Resize(dic.Count).Value = Application.Transpose(dic.keys)
End With
Application.ScreenUpdating = True
End Sub

 

[boomen]

Bij mij staat het onder Beeld-Macro's-Macro's weergeven. Heb Excel2010 en Windows7, misschien dat daar het verschil zit?

Nogmaals bedankt voor de behulpzame reactie overigens!

Ja graag gedaan haha.
TTLxITB misschien kun jij me helpen met de vraag die ik hier gesteld heb?

http://www.helpmij.nl/forum/showthread.php/835263-functie-probleem
Ik will vandaag nog een boek bestellen eigenlijk. Maar mag er Maar 1 voor VBA declareren. Als arme Student moet ik dus even een goede afweging maken

 

[TTLxITB]

Dag Boomen,

Dit is de eerste keer dat ik met VBA werk, dus ik heb geen idee wat ik je hierin zou moeten adviseren. Ik ga je vraag wel in de gaten houden, mocht er een goed boek naar voren komen, dan koop ik die zelf misschien ook nog wel

 

[arrie23]

Je moet de code op een andere plaats invoeren (hij moet gekoppeld worden aan het specifieke tabblad ipv aan het hele bestand). Dus niet onderaan de andere code plakken.
Zoals ik al schreef: "Ga in je Excel-bestand onderin op de naam van het tabblad staan ("Relatieoverzicht-b"). Rechter muisknop: Programmacode weergeven. Hierin onderstaande code plakken."

Probeer op deze manier eens de code beginnend met Private Sub... in te voeren en kijk dan eens wat er gebeurt

 

[TTLxITB]

Dag Arrie,

Excuus, ik had beter moeten lezen.
Ik heb de code in de programmeercode voor het werkblad geplaatst, maar nu wordt er helemaal niets meer geupdate (ook niet na alt+f8).
Namen van tabbladen uiteraard wel aangepast, dus daar zou het volgens mij niet aan moeten liggen.
Kan het kloppen dat de programmeercode van het werkblad automatisch ook in de VBA-module komt te staan? Als ik daar namelijk kijk, zie ik dezelfde 'private sub...' code staan, zonder dat ik daar iets heb aangepast.

Gr.

 

[arrie23]

Let op het zijn 2 aparte codes! Vanaf Sub Overzicht2() t/m de eerste end sub. En vanaf Private sub t/m de tweede end sub.

Als je via Alt+F11 in je VBA-editor kijkt zou het zo moeten zijn dat de eerste code (Sub Overzicht2()) onder Modules-Module1 staat. De code beginnend met Private Sub moet je hier verwijderen als deze hier nog staat. Als het goed is kun je dan via Alt+F8 de macro Overzicht2() aanroepen.

Als je in de VBA-editor linksboven dubbelklikt op Microsoft Excel-objecten - "Relatieoverzicht-b" moet hier alleen de code staan beginnend met Private Sub. De code Sub Overzicht(2) moet je hier eventueel verwijderen.

Dit zou ervoor moeten zorgen dat je via Alt+F8 handmatig macro Overzicht2() kan draaien. Maar daarnaast dat dezelfde macro altijd draait bij een wijziging van tabblad "Relatieoverzicht-b"

Het lijkt er overigens op dat je in de Private Sub code 'With Sheets("Relatieoverzicht ")' hebt staan, ofwel een spatie na Relatieoverzicht. In de Sub Overzicht2() code staat 'With Sheets("Relatieoverzicht")' zonder spatie. Ik vermoed dat je bij de eerste de spatie weg moet halen