Excel (VBA) Gegevens zoeken op verschillende tabbladen en "samenvatten"

[Robin111]

Hallo,

Ik loop een beetje vast bij het maken van een Excel/VBA sheet.

Zie bijlage "test"

De bedoeling van deze excel file is dat uit de tabbladen: Ref. Schema en Huidige Schema, diverse informatie wordt gefilterd en samengevat op de tabbladen van de week 'maandag', 'dinsdag', ....
Via het dashboard 'Selectie_scherm' moet worden bepaald welk weeknummer "gefilterd" moet worden en uiteindelijk moeten de tabbladen maandag t/m vrijdag op aparte A4 papieren worden geprint in landscape format.

Een aantal kolommen en rijen zijn als hulp rij/kolom. Deze worden uiteindelijk verborgen.

Waar ik nu vast loop is dat ik de kolom (j) van bijv het tabblad 'maandag' genaamd 'Ref.Omschrijving' niet (goed) ingevuld krijg voor de op dat moment betreffende weeknummer.:shocked:
Ik heb reeds geprobeerd met:
Voor weeknummer 1:
=ALS(ISFOUT(VERT.ZOEKEN($A5;'Ref. Schema'!$A$3:$BB$100;3;ONWAAR));"";VERT.ZOEKEN($A5;'Ref. Schema'!$A$3:$BB$100;3;ONWAAR))
Maar hoe combineer je deze formule indien het weeknummer verandert en kolom 3 dus ook moet veranderen?? (kolomnummer = weeknummer + 2)

Met ALS:
=ALS(ISNB(VERT.ZOEKEN(D4;$A$2:$B$5;2;ONWAAR));”";VERT.ZOEKEN(D4;$A$2:$B$5;2;ONWAAR))

Met INDEX:
=INDEX(Ref.Schema!$A$2:$BB$200;VERGELIJKEN(A5;Ref.Schema!A2:A200;0);VERGELIJKEN(E3;Ref.Schema!A2:BB2;0))

Wellicht kan ik nog veel meer verbeteren aan de formules en het ontwerp hiervan (ook de VBA).
Ik hou me aanbevolen.
De print optie ben ik nog geheel nog niet aan toegekomen. Als men hier al ideeen en of tips over heeft hoor ik het ook graag.

Alvast bedankt voor jullie hulp!

 

[MirjamHelp]

Hoi Robin111,

Kijk eens of dit je verder op weg helpt.
Ik heb de formule geplaatst die automatisch resultaat van de week geeft en je code iets opgeschoond zodat het makkelijker leesbaarder wordt.

Gr. Mirjam

 

[Robin111]

Beste Mirjam,

Bedankt voor je snelle reactie. :thumb:
Ik moet er even in duiken wat je precies in Excel en VBA aangepast hebt en hoe dit functioneerd.
Uiteraard wil ik het zelf ook begrijpen, zodat ik deze kennis ook later weer kan toepassen.

Gr.Robin

 

[MirjamHelp]

Ha Robin,

Even wat tijd genomen voor wat uitleg:

In het tabblad Maandag in cel J5 heb ik de formule veranderd in

=ALS.FOUT(VERT.ZOEKEN($A5;'Ref. Schema'!$A$3:$BB$85;$G$2+2;ONWAAR);"")

en vervolgens gekopieerd naar beneden, zover als jij ze ook gevuld had.

Deze formule
-zoekt op de waarde die in cel A5 staat (het apparaat nummer)
-zoekt in het bereik $A$3:$BB$85 op tabblad Ref. Schema
-zoekt in kolom nummer die de waarde heeft van cel $G$2 + 2. In G2 had je zelf al het weeknummer staan, dus daar wordt naar verwezen.
Wanneer het zoekresultaat een foutwaarde oplevert dan wordt er, voor het oog, niets weergegeven. De eigenlijke waarde is ""

In het VBA gedeelte heb ik inhoudelijk niets aan je code veranderd. Wat ik wel gedaan heb:
----------------------------------
lege modules verwijderd en de modules die zijn gebleven een duidelijkere naam gegeven. Dit kan je bij Eigenschappen doen. Met Functietoets 4 (F4) kom je bij (Name).
----------------------------------
alle routines die je had bij elkaar in de meest logische module geplaatst.
----------------------------------
Werken met With - End with. Een algemeen voorbeeld:

range("A1").value = 10
range("A1").clear

kan worden:

with range("A1")
   .value = 10
   .clear
end with

Een voorbeeld uit jouw code:

Sheets("Maandag").Range("A4:G1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Maandag").Range("A1:A2"), Unique:=True

vervangen door:

With Sheets("Maandag")
    .Range("A4:G1000").AdvancedFilter Action:=xlFilterInPlace, _
                    CriteriaRange:=.Range("A1:A2"), _
                    Unique:=True
End With

----------------------------------

Application.Run "Procedurenaam"

vervangen door

Call Procedurenaam

----------------------------------
Je gebruikt dezelfde code op verschillende plaatsen. Wanneer er iets veranderd moet worden, dien je dat op al die plaatsen te wijzigen. Dat is natuurlijk niet onderhoudsvriendelijk.
Daarom heb ik er een subroutine van gemaakt die je dan vanuit verschillende plaatsen aankunt roepen.

Zo heb je bijvoorbeeld voor elke dag (straks dus 7x) deze code:

Sub Unfilter_Maandag()
' Unfilter_Maandag Macro
    Sheets("Maandag").Select
    Selection.AutoFilter
End Sub

Sub Unfilter_Dinsdag()
' Unfilter_Dinsdag Macro
    Sheets("Dinsdag").Select
    Selection.AutoFilter
End Sub

die kun je vervangen door onderstaande code waaraan je toevoegt welke dag je meegeeft.

Private Sub Unfilter(strDag As String)
' Unfilter_dag Macro
    Sheets(strDag).Select
    Selection.AutoFilter
End Sub

Wanneer je de deze routine aanroept vertel je ook welke dag dat je wil bijv.:

Call Unfilter(strDag:="Maandag")

Ik hoop dat het zo enigszins duidelijker voor je is.
Gr. Mirjam

 

[Robin111]

Beste Mirjam, bedankt voor je uitvoerige uitleg. Helemaal duidelijk nu.
Deze kennis kan ik ook goed voor andere toepassingen gebruiken, top!!
Gr. Robin