Opgelost Lijst maken van begroting per rekening op datum

[pas260]

Beste,
Ik heb een bestand met alle inkomsten en uitgaven ( uitgaven zijn 3 categorieën ) , het geld komt en gaat van verschillende rekeningen kolom C ( in voorbeeld genoemd als rood , blauw , groen , geel en oranje ) .
Nu wil ik graag op het tabblad vooruitkijken dat ik een rekening kan selecteren in A1 en dat ik dan een overzicht op volgorde van datum krijg wat er bij komt en af gaat , met als doel dat ik kan zien hoeveel op betreffende rekening staat op een bepaalde datum.

Op tabblad vooruitkijken heb ik in kolom H t/m L gezet hoe ik het zou willen.
De datum moet echter naar drie cellen kijken eerst kolom A in begroting daarna maand Kolom D t/m O en vervolgens het jaar in cel A$1$ van tabblad begroting.
Is een cel zonder bedrag dan die er graag niet tussen 10-1-2026 jaarafrekening staat geen bedrag dus die liever er niet tussen.

Hoe kan ik dit het beste doen of moet ik dit anders aan pakken ?
Het originele bestand is inmiddels behoorlijk groot.

 

[AHulpje]

Probeer het hier eens mee. Selecteer de gewenste rekening op werkblad Vooruitkijken.

 

[pas260]

Hoi,
Ik krijg de melding dat de macro's geblokkeerd zijn vanwege een beveiliging risico

Wat ik van het resultaat kan zien is het goed maar ik kan in A1 wel een andere kleur kiezen ( bank ) maar dan veranderd eruit niks omdat de macro niks wil doen.

 

[AHulpje]

Je kunt via de verkenner de blokkering opheffen. Klik in de verkenner met de rechtermuisknop op de bestandsnaam, kies "Eigenschappen" en klik onderaan op "Blokkering opheffen".

 

[pas260]

Het is gelukt en doet precies wat ik wil , dank je wel .
Nu moet ik het vertalen naar mijn bestand.
Heb ik beide macro's nodig ?

Sub Vooruitkijken()
    Dim rij As Long
    Set Rng = Sheets("Begroting").Columns("A:A").Find(What:="Af", LookAt:=xlWhole)
    If Not Rng Is Nothing Then
        rijAf = Rng.Row
    Else
        MsgBox "'Af' niet gevonden in kolom A"
        Exit Sub
    End If
    Range("A3:E" & Cells(Rows.Count, 5).End(xlUp).Row).ClearContents
    rij = 2
    With Sheets("begroting")
        jaar = .Range("A1")
        rekening = Range("A1")
        For r = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(r, 3) = rekening Then
                vandaag = Date
                For maand = 1 To Month(Date)
                    datum = DateSerial(jaar, maand, .Cells(r, 1))
                    If datum <= vandaag Then
                        bedrag = .Cells(r, maand + 3)
                        If bedrag <> 0 Then
                            rij = rij + 1
                            Cells(rij, 1) = datum
                            Cells(rij, 2) = .Cells(r, 2)
                            If r < rijAf Then
                                Cells(rij, 3) = bedrag  'inkomsten
                            Else
                                Cells(rij, 4) = bedrag  'uitgaven
                            End If
                        End If
                    End If
                Next
            End If
        Next
    End With
    With ActiveWorkbook.Worksheets("Vooruitkijken").Sort
        .SetRange Range("A3:D" & rij)
        .Apply
    End With
    Application.CutCopyMode = False
    Range("E3").FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("E4").FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
    Range("E4").AutoFill Destination:=Range("E4:E" & rij)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Vooruitkijken
End Sub

 

[AHulpje]

Als deze werkwijze je bevalt dan heb je beide macro's nodig. De Worksheet_Change gebeurtenis start de 'echte' macro Vooruitkijken. Je zou de macro ook met de hand kunnen starten, maar dan is het verstandig om de macro iets aan te passen zodat het opruimen van de oude resultaten en het plaatsen en sorteren van de nieuwe resultaten alleen op het werkblad Vooruitkijken plaatsvinden. Indien gewenst kan ik dat voor je aanpassen. Je zou de macro ook via een knop kunnen starten. Let ook op de namen van de werkbladen als die anders zijn in je 'grote' bestand.

 

[pas260]

Bedankt alvast.
De werkwijze bevalt mij zeer zeker.
Ga morgen er goed voor zitten want in het echte bestand staan nog extra tussen kolommen .
Laat morgen weten of het gelukt is.

 

[AHulpje]

Bijgaande versie is iets sneller en robuuster. Het hele werkblad Begroting wordt nu in een array geladen. De macro kan nu eventueel ook vanuit andere werkbladen worden gestart.

 

[pas260]

Hoi,
Bij mij pakt hij tot de maand mei , klopt dat ?
Kan ik hem instellen dat hij tot en met december loopt ?

 

[AHulpje]

Deze versie verwerkt alle maanden, de vorige versie ging tot de huidige datum.

 

[snb]

Toch zou je dit met 1 dynamische tabel voor de gegevens moeten doen en vervolgens met een draaitabel en slicers alle mogelijke prognoses maken. Excel is daarop ontworpen, zodat je geen VBA hulp nodig hebt.

 

[cow18]

voor de grap een beetje uitgebreid

EDIT : dag SNB, eigenlijk heb ik je dynamische tabel gemaakt aan de hand van de gegevens

 

[snb]

@cow18

Dat is zeker het geval .
in plaats van 6 kleurkolommen zou ik echter 1 kolom 'bank' maken met de kleuren als waarden in die kolom.
Heb je gedacht aan een draaitabel met een draaigrafiek ?

 

[peter59]

Een beetje gestoeid met power query.
Zie bijlage voor het resultaat.
De tabellen in de tab "Begroting" kun je gewoon blijven gebruiken. Als daar iets verandert, klik dan in de tab "Vooruit kijken" ergens in de draaitabel en vervolgens op "Alles verversen".

 

[pas260]

Bedankt voor alle oplossingen .
De laatste is voor mij meer dan perfect , bedankt

 

[cow18]

alleen niet vergeten de query te vernieuwen (handmatig of met macro) als je de gegevens aanpast