Helpmij.nl
Helpmij.nl
Helpmij.nl
Steun Helpmij.nl! Klik hier     Computerprobleem? Klik hier!

Quote

Pagina 1 van 2 1 2 LaatsteLaatste
Weergeven resultaten 1 tot 20 van 34

Onderwerp: VBA-code i.p.v Index formules

  1. #1
    Vraag is opgelost

    VBA-code i.p.v Index formules

    Hallo,
    in het volgende voorbeeld bestand een tabblad met banktransacties en een tabblad waar ik met behulp van index-formules het resultaat per bankrekening bereken (saldo eind maand minus saldo begin maand).
    Ik zou dit mbv vba willen doen. Zou iemand voor mij een begin kunnen maken (met array's?)?
    Attached Files Attached Files
    Laatst aangepast door jansm : 20 juni 2017 om 00:26

  2. #2
    Waarom VBA?
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Dag Jan Karel,
    snelheid!
    Laatst aangepast door jansm : 20 juni 2017 om 12:54

  4. #4
    VBA is zelden sneller dan Excel formules. Wellicht dat dit met een draaitabel kan worden gemaakt? Ik had een poging gewaagd middels "som van bij" minus "som van af" en dat totaliseren per maand, maar dat kwam van geen kanten overeen met jouw formules (zijn die zeker juist?), dus toen heb ik het opgegeven.
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    Giga Senior
    Verenigingslid

    Geregistreerd
    19 april 2007
    Locatie
    den haag
    Afstand tot server
    ±111 km
    misschien is het handig als je vertelt wat er berekend moet worden. dan hoef ik die formules van jou niet uit te pluizen.
    Laatst aangepast door sylvester-ponte : 20 juni 2017 om 15:18
    groeten Sylvester Ponten

  6. #6
    Giga Honourable Senior Member
    Verenigingslid

    Geregistreerd
    12 juni 2008
    Zo, ben je weer even van de straat:

    Code:
    Sub M_snb()
      sn = Sheets("trans").Cells(1).CurrentRegion
      
      With CreateObject("scripting.dictionary")
         For j = 2 To UBound(sn)
             sp = Array(1 * sn(j, 1), sn(j, 8), 1 * sn(j, 1), sn(j, 8), 0, sn(j, 4))
             If .exists(sn(j, 4) & "_" & Format(sn(j, 1), "yyyymm")) Then
                st = .Item(sn(j, 4) & "_" & Format(sn(j, 1), "yyyymm"))
                If st(0) < sp(0) Then
                   sp(0) = st(0)
                   sp(1) = st(1)
                End If
                If st(2) > sp(2) Then
                   sp(2) = st(2)
                   sp(3) = st(3)
                End If
                sp(4) = sp(3) - sp(1)
             End If
             
             .Item(sn(j, 4) & "_" & Format(sn(j, 1), "yyyymm")) = sp
         Next
         Sheets("saldo").Cells(20, 1).Resize(.Count - 1, 6) = Application.Index(.items, 0, 0)
      End With
    End Sub
    Laatst aangepast door snb : 20 juni 2017 om 15:47
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/showthread.php/635117-Tips-om-de-helpers-in-deze-sectie-van-dienst-te-zijn
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  7. #7
    Voor zover ik de toegepaste formules begrijp wordt gezocht naar de eerst voorkomende waarde in de kolom Saldo waarbij wordt voldaan aan een aantal criteria. Dit lijkt mij niet juist. Ik kan er helemaal naast zitten. i.i.g. kom ik, als ik met sumproduct m'n eigen logica volg, op heel andere getallen uit. Misschien dezelfde als jkp?

    Gebruikte formule:
    Code:
    =SUMPRODUCT(--(TRANS!$D$2:$D$921=SALDO!C$2)*(MONTH(TRANS!$A$2:$A$921)=MONTH($A3))*((YEAR(TRANS!$A$2:$A$921)=YEAR($A3)))*((TRANS!$F$2:$F$921)-(TRANS!$G$2:$G$921)))
    Mogelijk moet je door mij voorgestelde formules vertalen. Bv. via deze link.

    Ik ben dan wel senior maar niet te oud om te leren. Ik weet dus ook niet alles ...

  8. #8
    bedankt voor moeite JK.
    Sylvester, tabblad TRANS is volgens mij duidelijk, de data waaruit geput wordt.
    De bedoeling is om van de 3 rekeningnummers het resultaat per maand te berekenen (resultaat = saldo laatste dag minus saldo eerste dag).
    Data waarmee gerekend wordt staan in TRANS-kolom C (datum), D (rekeningnr) en H (saldo na transactie). Als van een rekeningnr op de bepalende dag geen transactie wordt gevonden dan wordt voor de berekening:
    - voor de eerste dag van de maand het saldo (H) na de laatste transactie in de maand daarvoor genomen
    - voor de laatste dag van de maand het saldo (H) na de laatst gevonden transactie van betreffende maand genomen.

    Dus, bijv voor C3 geldt: het resultaat van rekeningnr 12345001 over de maand mei 2016: 1418.13 - 1309.04=109.09

  9. #9
    Oef, peter en snb. Zal ik straks ff op mij in laten werken.
    De getallen in voorbeeld zijn juist hoor. Hopelijk dat mijn vorige bericht dit verduidelijkt

  10. #10
    Ik ben benieuwd hoe je omgaat met meerdere entry's op de eerste of de laatste dag. Volgens mij pak je dan de eerste die je tegenkomt (zo werkt de INDEX-formule) terwijl je uiteindelijke saldo dus hoger of lager kan zijn.

    Mijn formule hoef je dan niet naar te kijken. Deze is gebaseerd op inkomsten/ uitgaven in de betreffende maand voor de betreffende rekening.
    Mogelijk moet je door mij voorgestelde formules vertalen. Bv. via deze link.

    Ik ben dan wel senior maar niet te oud om te leren. Ik weet dus ook niet alles ...

  11. #11
    Ik zou denken dat je berekening gelijk moet zijn aan de som van bij - af, gegroepeerd op de rentedatum anders is er echt iets mis met de data?
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  12. #12
    @snb
    code nog niet kunnen doorgronden, maar is ook geen schande toch? Resultaat nog niet kunnen herkennen in wat het zou moeten zijn. Ben dus nog wel ff van de straat! Zal er ongetwijfeld nog wel op terugkomen als dat van je mag.
    @Peter
    Zie mijn voorbeeld C3.
    Van betreffende rekening is op 31 mei 2016 geen transactie aanwezig. Dan wordt, zoals jij zegt, de eerstvolgende genomen op 29 mei 2016. Het saldo na deze transactie is ook het saldo op 31 mei 2016 (logica, toch?).
    Voor het saldo op 1 mei 2016 wordt gezocht op (1 mei 2016 -1 dag). Dat resulteert dan in saldo na de laatste transactie vóór 1 mei 2016 dus het startsaldo voor de maand mei. Ben benieuwd naar jouw mening hierover.

  13. #13
    jk, ook benieuwd naar jouw reactie op #12

  14. #14
    snb, voor mij te veel van het goede.
    Je gooit TRANS in een array, daarnaast maak je een array sp met seriële datum, saldo, seriële datum, saldo, 0 en rekeningnr.. Van wat daarna komt kan ik geen chocola van maken.
    Hij doet niet wat ik in #8 en #12 beschrijf. Het enige wat ik dacht te kunnen aanpassen was sn(j,1) in sn(j,3), datum in kolom 3.

  15. #15
    Giga Honourable Senior Member
    Verenigingslid

    Geregistreerd
    12 juni 2008
    Zoals je kunt zien heb ik mijn suggestie geplaatst voordat jij de specificaties verried.
    De suggestie is ook niet als oplossing bedoeld, maar als suggestie hoe je het aan zou moeten pakken. Jouw specificaties zijn nl. niet al te verstandig.
    Je vroeg om VBA code; die heb je gekregen en tegelijkertijd een belangrijke suggestie om wat je wil bereiken beter aan te pakken.
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/showthread.php/635117-Tips-om-de-helpers-in-deze-sectie-van-dienst-te-zijn
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  16. #16
    snb, dank voor jouw reactie.
    Over mijn specificaties in #8 en #12: het was mijn bedoeling om dat bij de vraagstelling aan te gegeven. Maar dat is dus te summier geweest begrijp ik. Ik heb die fout wel eens eerder hier gemaakt. Toch geef ik in #1 aan hoe de getoonde resultaten berekend worden hoor.
    Als het erop lijkt dat ik negatief gereageerd heb op jouw code dan komt dat ook verkeerd over want dat is absoluut niet mijn bedoeling geweest. Wat ik aangeef is het enige dat ik van de code denk te begrijpen.
    Zou je me nog kunnen verklaren waarom je vindt dat het onverstandig is? Dat zou ik graag willen weten.
    Mag ik je nog vragen om bij de code wat uitleg te geven? Je hebt dus een andere aanpak en daar ben ik oprecht in geïnteresseerd.
    PS heb "VBA voor smarties" voor me gehad maar ben kennelijk niet smart genoeg!

  17. #17
    Giga Senior
    Verenigingslid

    Geregistreerd
    19 april 2007
    Locatie
    den haag
    Afstand tot server
    ±111 km
    jan, kun je uitleggen wat er mis gaat met mijn uitkomsten?( de toelichting zit in de uitkomst) vergelijk de grijze cellen.
    Attached Files Attached Files
    groeten Sylvester Ponten

  18. #18
    Ik vermoed dat het te maken heeft met de sortering van je gegevens, je zoekt in de tabel op rentedatum, maar deze zijn niet chronologisch gesorteerd. Het saldo dat je daardoor vindt is niet per se het eindsaldo van die datum.
    Ik blijf erbij dat de juiste uitkomst zou moeten zijn de som van alle bij-tjes in de maand (van de rentedatum) min de som van alle af-jes in diezelfde maand. Als dat niet zo is begrijp ik geen snars van jouw bank en zou ik een andere zoeken :-)
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  19. #19
    Giga Senior
    Verenigingslid

    Geregistreerd
    19 april 2007
    Locatie
    den haag
    Afstand tot server
    ±111 km
    deze komt misschien meer in de buurt, de datums kloppen aardig nou nog de eind waarden.
    Attached Files Attached Files
    Laatst aangepast door sylvester-ponte : 21 juni 2017 om 17:37
    groeten Sylvester Ponten

  20. #20
    Sylvester, bedankt. Mijn reactie op # 17, zie bijlage (2 berekeningen om te vergelijkingen). Naar jouw laatste posting kijk ik vanavond
    Over boek- en rentedatum het volgende.
    Wat ik ervaar is dat de transactiedatum van mijn betalingen altijd als rentedatum wordt geregistreerd. De boekdatum wordt soms later gelegd, en waarom? Geen idee. Sommige transacties op zaterdag krijgen soms maandag als boekdatum. Waarschijnlijk wordt het verwerken van transacties door de bank soms uitgesteld en daardoor ook de boekdatum.
    Als ik een betrouwbaar resultaat van een rekeningnr. per maand wil berekenen dan moet ik wel uitgaan van de rentedatum (optie is nog om eerst te sorteren op rentedatum). Ben benieuwd of er lezers zijn die dezelfde ervaring hebben met hun bank. De opmerking dat ik verkeerd bezig ben voor wat betreft datum-keuze lijkt mij dus ongegrond. Sta open voor opmerkingen/commentaar.

    TestE_Sylv.xlsb

  21. Dit topic is automatisch gesloten omdat er sinds vier maanden niet meer op gereageerd is.

    Indien gewenst kan de topicstarter een verzoek tot heropening indienen.
Pagina 1 van 2 1 2 LaatsteLaatste

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren
Aanbiedingen