• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

VBA-code i.p.v Index formules

Status
Niet open voor verdere reacties.

jansm

Gebruiker
Lid geworden
2 apr 2014
Berichten
421
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?)?
 

Bijlagen

  • TestE.xlsb
    45,8 KB · Weergaven: 68
Laatst bewerkt:
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.
 
misschien is het handig als je vertelt wat er berekend moet worden. dan hoef ik die formules van jou niet uit te pluizen.
 
Laatst bewerkt:
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 bewerkt:
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)))
 
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
 
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
 
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.
 
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?
 
@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.
 
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.
 
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.
 
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!
 
jan, kun je uitleggen wat er mis gaat met mijn uitkomsten?( de toelichting zit in de uitkomst) vergelijk de grijze cellen.
 

Bijlagen

  • TestE (2.xlsb
    54,1 KB · Weergaven: 38
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 :)
 
deze komt misschien meer in de buurt, de datums kloppen aardig nou nog de eind waarden.
 

Bijlagen

  • TestE (3.xlsb
    54,1 KB · Weergaven: 34
Laatst bewerkt:
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.

Bekijk bijlage TestE_Sylv.xlsb
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan