Cellen optellen/aftrekken

[Pascal12]

Goedemorgen,

Ik heb een probleempje waarvan ik denk dat de oplossing niet al te moeilijk moet zijn, maar ik kan hem zelf helaas niet verzinnen.

Ik heb een journaalpost die gecodeerd moet worden en bij daarbij wil ik bij een bepaalde codering dat er bedragen bij elkaar worden opgeteld en afgetrokken.
De codering krijg ik wel via een formule voor elkaar, maar het bedrag ernaast krijgen lukt me niet. Dit komt omdat de file wordt aangeleverd met samengevoegde cellen en het aantal regels verschilt ook per keer.

Ik heb een gedeelte van de sheet bijgevoegd en zal uitleggen wat ik wil bereiken.

In kolom A staan meegeleverde codes, naar aanleiding hiervan heb ik de codering gemaakt in cel Z
Nu wil ik naast elke codering 402000 per afdeling een totaalsom maken in cel AA van de volgende regels:

Regel met code 40100 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q83 - T83)
Regel met code 40101 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q88 - T88)
Regel met code 40102 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q94 - T94)

In dit geval wil ik dus het bedrag 2844,98 in cel AA83 zien verschijnen

Aangezien de codes in cel A per afdeling voorkomen heb ik denk ik een bereik nodig zodat hij niet de verschillende afdelingen bij elkaar gaat optellen.

Bij deze het bestand: Bekijk bijlage Journaalpost.xlsx

Hopelijk is het een beetje duidelijk en kunnen jullie mij hiermee helpen.

Alvast bedankt,
Pascal

 

[sylvester-ponte]

in je voorbeeld staan in kolommen q en t geen waarden
volgens je uit leg zo daar iets moeten staan.

kun je de waarden in Q en T toevoegen in je voorbeeld?
en wil je dan ook de uitkomsten toevoegen die je berekend wilt hebben.

 

[Pascal12]

Het lijkt dat er geen waarde in staat doordat het samengevoegde cellen zijn. Als ik het voorbeeld open en bijvoorbeeld naar regel 83 kijk dan zie je als het goed is de volgende waarden staan

A83 = 40100
D83 = ZorgVerzekeringsWet
Q83 = 1160,93
T83 = 0
Z83 = 402000

in AA83 wil ik dan volgende uitkomst hebben: 2.844,98

opgebouwd uit:

Regel met code 40100 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q83 - T83) 1160,93 - 0
Regel met code 40101 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q88 - T88) 693,43 - 0
Regel met code 40102 in kolom A, dan moet hij de volgende som maken: het bedrag in cel Q van dezelfde regel min het bedrag van cel T van dezelfde regel (Q94 - T94) 1010,69 - 20,07

Hopelijk kan je nu vinden wat ik bedoel?

Gr.
Pascal

 

[Haije]

Pascal,

sorry, maar op basis waarvan bepaal je dat de waarden van de regels 83, 88 en 94 hier van belang zijn en niet ook bv regel 95?
Krijg je dit bestand zo aangeleverd?

Wat doet de formule in kolom Z?

 

[Pascal12]

De regels die ik nodig heb ik bepaal ik naar aanleiding van de codes in kolom A

Ik heb het bedrag nodig van de regels waarin de codes 40100, 40101 en 40102 voorkomen

In voorbeeld wat jij zegt:

Regel 94 heeft code 40102, dus de bedragen 1010,69 en bedragen 20,07
De regels die hieronder staan (onder andere regel 95) hebben geen code 40102 in kolom A staan, dus deze heb ik niet nodig. (Regels 95 t/m 105 laat zien hoe de bedragen 1010,69 en bedrag 20,07 zijn opgebouwd)


De formule in kolom Z geeft mij de codering van het grootboek waarop de boekingen in ons systeem geboekt moeten worden. Elke code in kolom A moet op een bepaald grootboek geboekt worden en dat laat deze formule mij zien.

 

[Haije]

Pascal,

in de bijlage een mogelijk oplossing. Daarvoor is in kolom Y een formule geplaatst om te bepalen of een regel wel of niet meegenomen moet worden en zijn tywee bereiken benoemd: checkafda en checkafdB

 

[Pascal12]

Het werkt nog niet helemaal hoe ik het graag zie.

Is het mogelijk om het bereik op een andere manier te bepalen? Wat ik namelijk graag zou zien is dat de formule checkt in kolom A naar code 40100 en dan als bereik bijvoorbeeld nog 25 regels naar beneden zoekt.

Dus in je voorbeeld vindt hij in cel A83 code 40100, dan begint het bereik hier en het bereik eindigt dan bijvoorbeeld in cel A108. In dit bereik zoekt hij dan naar de codes 40100, 40101 en 40102 en daar maakt hij dan de rekensom van.

Op deze manier hoef ik volgens mij niet elke maand de bereiknamen eerst gaan invoeren. Ik hoor graag of dat nog mogelijk is.

In ieder geval alvast voor je hulp tot nu toe :thumb:

 

[Haije]

Pascal,

gaat het altijd alleen om 40100, 40101 en 40102?
En altijd om afdeling 1, of ook om afdeling 2?

 

[Pascal12]

Het gaat altijd alleen om 40100, 40101 en 40102, maar er zijn inderdaad meerdere afdelingen. De sheet die ik geupload had is maar een klein gedeeltje van de totale sheet.

Er zijn iets van 15 afdelingen en in elke afdeling komen de codes 40100, 40101 en 40102 voor. Per afdeling wil ik dus dat hij de bedragen van codes 40100, 40101 en 40102 berekend.

 

[Haije]

En mag dat evt ook mbv een macro?

 

[Pascal12]

Wat mij betreft wel, zolang het werkt

 

[Haije]

Staan de codes 40100, 40101 en 40102 altijd in deze volgorde?

 

[Pascal12]

ja, wel in die volgorde, maar het aantal regels tussen de codes kan per afdeling nog wel eens een paar regeltjes verschillen.

 

[Haije]

Pascal,

zie de bijlage voor een manier. LET OP: macro's toestaan

 

[Pascal12]

Hoi Haije

Dit werkt ******* goed, ik snap helaas niets van deze macro, daar ben ik ook nog niet zo in thuis, maar hij doet wat hij moet doen.

Mag ik je nog een vraag stellen, zou je ook de formule van de codering erin kunnen verwerken?
Ik dacht dit handmatig te kunnen toevoegen in jouw macro, maar ik krijg dan een foutmelding.

Sub totaliseren()
Sheets(2).Select
Columns("AA").ClearContents
rgl = Range("A1000000").End(xlUp).Row
For r = 0 To rgl
If Range("A1").Offset(r) = 40100 Then Call bereken(r)
Next r
MsgBox "Klaar"
End Sub
Sub bereken(r)

rg = r + 1
totaal = Cells(rg, 17).Value - Cells(rg, 20).Value
Range("A" & rg + 1).End(xlDown).Select
rg = ActiveCell.Row
totaal = totaal + Cells(rg, 17).Value - Cells(rg, 20).Value
Range("A" & rg + 1).End(xlDown).Select
rg = ActiveCell.Row
totaal = totaal + Cells(rg, 17).Value - Cells(rg, 20).Value
Cells(r + 1, 27) = totaal
r = rg
  
  Workbooks.Open Filename:= _
        "V:\Administratie\Rapportages\2014\info tabel.xlsx"
    Selection.Copy
    Windows("Journaalpost hs.xlsm").Activate
    ActiveWindow.WindowState = xlNormal
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    [B]Selection.AutoFill Destination:=Range("Z25:Z2377"), Type:=xlFillDefault[/B]
    Range("Z25:Z2377").Select
    ActiveWindow.SmallScroll Down:=-33
    Range("Z23").Select


End Sub

maar dan valt de macro over de dik gedrukte regel. Als ik de macro voor de codering uitvoer zonder jou macro, dan werkt het wel.
In mijn macro haalt hij de formule uit een ander excel bestand, maar als je dit kan verwerken zonder een ander excel bestand te open is ook goed.

Dit is de formule die de macro gebruikt:

=ALS(ISGETAL(VIND.ALLES("40000";($A36)));"401000";ALS(ISGETAL(VIND.ALLES("40001";($A36)));"401070";ALS(ISGETAL(VIND.ALLES("40006";($A36)));"401015";ALS(ISGETAL(VIND.ALLES("40017";($A36)));"410200";ALS(ISGETAL(VIND.ALLES("40018";($A36)));"410210";ALS(ISGETAL(VIND.ALLES("40020";($A36)));"401010";ALS(ISGETAL(VIND.ALLES("40100";($A36)));"402000";ALS(ISGETAL(VIND.ALLES("40107";($A36)));"410700";ALS(ISGETAL(VIND.ALLES("40201";($A36)));"402100";ALS(ISGETAL(VIND.ALLES("42005";($A36)));"410200";ALS(ISGETAL(VIND.ALLES("42008";($A36)));"441050";ALS(ISGETAL(VIND.ALLES("42012";($A36)));"230510";ALS(ISGETAL(VIND.ALLES("DEC";($A36)));"12";"")))))))))))))

Mocht het niet lukken, is ook goed hoor. Ik ben hier al heel erg blij mee!

 

[Haije]

Pascal,

in het startblad heb ik nu een tabelletje toegevoegd waarin de waarden in kolom A en de corresponderende waarde die in kolom Z moet komen.
Daarnaast de macro iets aangepast, waardoor zowel de totalisering als de omcodering in één slag plaatsvinden

 

[Pascal12]

Ik heb hem even getest, maar hij geeft nu alleen de codering in kolom Z wanneer hij 40100 in kolom A heeft gevonden.

Voor de codes in kolom A
40000
40001
40006
40017
40018
40020
40107
40201
42005
42008
42012

Zie ik nu nog niet de coderingen verschijnen in kolom Z

 

[Haije]

Pascal,

zie bijlage

 

[Pascal12]

Hoi Haije,

Hij doet het nog steeds niet overal, maar nu alleen bij de de codes 40100, 40101 en 40102. (Bij codes 40101 en 40102 hoeft hij het niet te doen, omdat deze onderdeel van 40100 zijn)

Ik verwacht ook dat de coderingen verschijnen in kolom Z voor de overige codes in kolom A

Dus als in kolom A in de regel 40000 staat verwacht ik in kolom Z in dezelfde regel 401000
Dus als in kolom A in de regel 40001 staat verwacht ik in kolom Z in dezelfde regel 401070
etc..

 

[Haije]

Hoi Pascal,

blijkbaar heb ik je niet goed begrepen.
Ik hoop dat deze wel voldoet aan je verwachtingen