Automatisering van facturatie en werkbonnen

[Basto]

Eerder heb ik hier de vraag gesteld voor de "filtering" van data. In ditzelfde bestand komen (veel) meer functies via VBA en daar ondervind ik soms wat moeilijkheden.
Leek mij slimmer om dan maar een algemene post aan te maken waar ik de vragen in kan verwerken.

Snelle uitleg: ik wil een bestand maken waarin ik makkelijk facturen en werkbonnen kan aanmaken. In het bestand waarin ik nu bezig ben vul ik dan de gegevens in en bij een druk op de knop worden deze gegevens in een nieuw bestand weggeschreven en opgeslagen. Dit bestand komt later in de cloud zodat enkele andere personen hier ook in kunnen werken. Dat is het uiteindelijke doel.

Mijn excel bestand bestaat uit een paar sheets. In sheet 1 (invullijst) wil ik in bepaalde cellen een keuze lijst tonen met daarin "zoekcodes" die van een andere sheet (productlijst) afkomstig zijn. Dit wil ik doen via een macro omdat de keuze voor een dropdown menu afhangt van wat andere zaken.
Vervolgens zoek ik aan de hand van de zoekcode andere informatie op. De laatste stap moet me wel lukken maar ik krijg het nog niet voor elkaar om een keuzelijst te maken met waardes uit een andere sheet. Als ik dit via valideren en dan lijst, probeer dan krijg ik de melding dat ik geen gegevens uit een ander werkblad mag gebruiken.

Iemand een idee hoe ik dat in VBA voor elkaar krijg?

Ter info, ik werk nu met Excel 2003. Ik ben van plan om binnenkort over te stappen op office 2013.

Bekijk bijlage Datalijst.xls

 

[Hans123]

Niet via macro, maar zet eens een "=" voor zoekcode in je validatielijst...............

 

[Cobbe]

De validatie met gegevens van een ander blad dienen via een naambereik te gebeuren.
Heb deze voor u gedaan - dynamisch - dat betekent dat je gewoon gegevens onderaan kunt toevoegen.

 

[Basto]

Hans en Cobbe, bedankt voor de tips. Cobbe, ik zie dat u inderdaad in cel B21 en B22 een keuzelijst heeft aangemaakt, en de zoek functies ook gelijk heeft ingebouwd! Ik kan er echter niet achter komen hoe de keuzelijst heeft aangemaakt, dit zou ik toch graag weten. Enerzijds zodat ik dit in het vervolg zelf kan doen, anderzijds om uit te kunnen zoeken hoe ik dit met behulp van een macro kan doen.

In ieder geval bedankt voor de hulp!

 

[Cobbe]

Via Namen - Namen Beheren kan je zien in verwijst naar - het bereik bepaald via Indirect en aantalarg.

 

[Basto]

Sorry Cobbe maar waar vind ik "namen", via welk menu item kom ik daar?

Edit: gevonden, "Invoegen" => "Naam" => "Definiëren"

 

[Basto]

Ik ben inmiddels wat verder, zie bijgevoegd bestand. Als ik nu op de knop factuur maken klik dan wordt op rij 21 alles ingevuld wat ik wil. Nu probeer ik een functie te maken die me nog niet echt wil lukken.
Als ik in cel B21 een product kies wil ik dat op regel 22 de formules worden geplaatst zoals in regel 21. Met andere woorden: als ik in regel 21 een product heb ingevoerd dan zou de volgende regel zo geconfigureerd moeten worden dat ik daar ook een product kan selecteren. Hij moet dus steeds een nieuwe regel aanmaken als ik de laatste regel in heb gevuld.

Ik heb al wat zaken geprobeerd maar de ene keer gebeurt er niets, de andere keer wordt mijn hele range (1999 regels) vol geschreven, dat is nu net niet de bedoeling.

 

[Basto]

De afgelopen dagen wat lopen stoeien maar helaas kom ik niet verder. Het doel is als in cel A21 een aantal wordt ingevuld dat dan in regel 22 alle formules automatisch worden geplaatst. Plaats ik vervolgens in cel A22 een aantal dan moet in regel 23 alle formules worden geplaatst. Het plaatsen van de formules lukt.

Ik krijg het echter niet voor elkaar om een actie uit te voeren bij een wijziging in cel A21. Ik heb nu in "Thisworkbook" de volgende Sub geplaatst:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A21:A1999")) Is Nothing Then Update
End Sub

De functie Update:

Sub Update()
           VolgNummer = VolgNummer + 1
           Range("C" & VolgNummer + 1).Value = "=VLOOKUP(C2,Productlijst!R2C1:R150C4,2,0)"  'Formule verticaal zoeken plaatsen zodat in cel C. juiste eenheid komt
           Range("D" & VolgNummer + 1).Value = "=VLOOKUP(C2,Productlijst!R2C1:R150C4,3,0)"  'Formule verticaal zoeken plaatsen zodat in cel D. juiste product komt
           Range("E" & (VolgNummer + 1)).Value = "=IF(C3=""hr"",R[-10]C[3],VLOOKUP(C2,Productlijst!R2C1:R150C4,4,0))" 'C3 staat voor kolom E plus 3 => H; R[-10] staat voor huidige rij -10 => 21 min 10 is 11 => H11
           Range("E" & (VolgNummer + 1)).NumberFormat = "$ #,##0.00"   'Opmaak van de cel als valuta met een euro teken en afgerond op twee cijfers achter de komma
           Range("F" & (VolgNummer + 1)).Value = "=" & "A" & (VolgNummer + 1) & "*" & "E" & (VolgNummer + 1)   'Formule zetten in cel F21. Dit kan ook met .formula maar die update alleen bij indrukken van de button. Dit update continu.
           Range("F" & (VolgNummer + 1)).NumberFormat = "$ #,##0.00"
 End Sub

Zodra ik nu een nieuwe factuur aanmaak wordt mijn hele werkblad volgeschreven met formules.

Wat ik er van begrijp is de Sub worksheet change alleen wordt aangeroepen als er iets wijzigt in kolom A. Zodra ik dus in A21 een aantal invul wordt deze functie actief. Die verwijst door naar de functie update die wat formules plaatst. Daar zou het moeten stoppen aangezien ik in kolom A geen automatische zaken plaats.

Of ik begrijp de functie niet van worksheet change of ik maak ergens anders een fout. Iemand die me een zetje in de juiste richting kan geven?

Edit:
Door NOT toe te voegen aan het If intersect statement werkt het nu bijna goed. Als ik nu een aantal invul in cel A21 worden de formules naar rij 22 gekopieerd. Als ik echter de cellen leeg maak door ze te selecteren en op delete te drukken, dan worden de formules ook naar de volgende regel gekopieerd. Dat is niet de bedoeling.

 

[VenA]

Voor code zijn de code tags uitgevonden. Gebruik deze dan svp ook. Het plaatsen van code in Thisworkbook lijkt mij in dit geval ook niet de meest logische plek. Vul in het voorbeeldje in B21 en in C21 wat in en dan denk ik dat het invullen hetgeen doet wat jij vraagt.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B21:B1999")) Is Nothing And Target.Count = 1 Then
    Application.EnableEvents = False
    With Target
        .Offset(, -1) = "#" & Target.Row - 20
        .Offset(, 1).Resize(1, 5).AutoFill .Offset(, 1).Resize(2, 5)
        .Offset(1).Resize(1, 2).ClearContents
    End With
    Application.EnableEvents = True
End If
End Sub

 

[Basto]

Je hebt gelijk, code tags maakt het overzichtelijker. Heb mijn vorige post aangepast. Bedankt voor je voorbeeld code, ik heb dit geprobeerd en dit lijkt grotendeels te werken.
Ik zou ook graag weten wat de code doet, simpel kopiëren wordt ik niet wijzer van.

Zoals ik het zie:

Application.EnableEvents =  False

zorgt ervoor dat zodra de functie wordt aangeroepen deze niet nogmaals kan worden aangeroepen (voorkomen van een loop).

    With Target
        .Offset(, -1) = "#" & Target.Row - 20
        .Offset(, 1).Resize(1, 5).AutoFill .Offset(, 1).Resize(2, 5)
        .Offset(1).Resize(1, 2).ClearContents
    End With

Dit kan ik wat minder goed volgen.
De tweede regel plaatst blijkbaar "#1" in cel A21. Aan de code te zien kijk je naar de actuele regel (regel 21) en daar trek je dan 20 vanaf. Ook lijk je daar 1 kolom naar rechts te schuiven waardoor we in kolom A terecht komen.
Van regel drie kan ik helaas niet veel van maken
Regel vier lijkt kolom C op de nieuw ingevoerde regel leeg te maken. Ook daar staat een rezise instructie, maar de functie daarvan kan ik niet volgen.

Vervolgens wordt

Application.EnableEvents =  True

gezet zodat de functie weer actief wordt.

Zou je me kunnen uitleggen wat er op de derde en vierde regel gebeurt? En klopt de rest van mijn gedachtegang?

Edit:
Ik zit nog eens naar de code te kijken en ik zie dat de Update functie helemaal niet wordt aangeroepen. Toch komen de formules wel in de juiste cellen, iets waar ik de Update functie voor gebruikte.

 

[VenA]

Target staat in kolom B. Om het volgnummer in kolom A te krijgen moet je dus 1 positie naar links Offset(,-1). Als je in rij 21 begint en het eerste volgnummer moet 1 zijn dan moet je daar inderdaad 20 van aftrekken

Resize vergroot het bereik vanaf een bepaalde positie waarbij de 1 ook weggelaten mag worden. Om te proberen

Sub VenA()
  MsgBox [a1].Resize(, 5).Address
  MsgBox [a1].Resize(5).Address
  MsgBox [a1].Resize(5, 5).Address
End Sub

Autofill is hetzelfde als de vulgreep. En verklaart ook jouw edit.

.Offset(1).Resize(1, 2).ClearContents

is niet correct dit moet worden

.Offset(1).ClearContents

anders ben je de formule voor de eenheid kwijt.

Als je er een echte tabel van maakt dan heb je overigens helemaal geen code nodig.

Edit met resize kan je ook een bereik verkleinen.

 

[Basto]

Dank voor de uitleg! Moet het nog wel een paar keer doorlezen om te begrijpen maar ik heb de tijd.
Ik ben al de halve middag aan het klungelen om de eenheid er bij in te krijgen omdat ik ook merkte dat deze niet mee gekopieerd werd. Nu werkt het als een zonnetje!
Zelf was ik al aan het stoeien met andere methodes maar als ik dan een regel verwijderde werd deze vervolgens gewoon leeg gelaten (logisch omdat mijn volgnummer niet wordt aangepast). Met jouw code neemt ie de lege regel weer mee.

Met Rezise schuif je dus een aantal rijen of kolommen op en selecteert alle cellen binnen het bereik? De eerste van jouw voorbeeld selecteert A1 t/m E1 en de tweede A1 t/m A5.
Rezise ( , x) selecteert de eerst volgende x kolommen en Rezise ( x , ) de eerst volgende x rijen.

Bedankt voor de hulp! Kan ik weer verder puzzelen, ik kom vast nog wel wat andere obstakels tegen.

 

[VenA]

Graag gedaan en succes met de andere obstakels.

 

[Basto]

VenA, in een eerder bericht zei u:

Als je er een echte tabel van maakt dan heb je overigens helemaal geen code nodig.

Hoe gaat dit in zijn werk?

Ik had er in mijn geval even niet bij stilgestaan dat we een hoog en laag tarief voor de BTW hebben. Nu krijg ik het hoogstwaarschijnlijk wel voor elkaar om de BTW te bepalen voor de producten en dit apart weer te geven (kan per product verschillen).
Er is echter één maar, als ik een aantal producten heb geselecteerd (er staan bijvoorbeeld 10 producten onder elkaar) en ik verwijder de laatste 3 regels, dan gaat het fout. Ik gebruik de variabele "VolgNummer" in mijn berekeningen en die ziet niet dat er drie rijen zijn verwijderd.

Als ik er, zoals u aangeeft, een echte tabel van maak, ben ik benieuwd of dit soort zaken makkelijker te ondervangen zijn.

 

[VenA]

Ik begrijp niet wat het volgnummer met de BTW bepaling en de hele berekening te maken heeft. Dus plaats maar weer een voorbeeldje.

 

[Basto]

Ik heb het nu anders opgelost. Mijn eerste idee was om per regel te kijken of er 6% of 21% BTW van toepassing was. Een IF lus zou dan moeten controleren of de huidige cel 6 of 21% was en dit dan bij het totale btw bedrag voor 6 of 21% optellen. Om dit te kunnen doen moet ik bijhouden op welke regel ik ben, vandaar de variabele "VolgNummer".
Nu heb ik drie extra kolommen aangemaakt; soort BTW (H/L), lage btw (bedrag), hoge btw (bedrag). Als ik het met een IF lus zou maken dan kunnen de laatste twee kolommen vervallen omdat ik dit in de IF lus dan direct optel. Iets in de trant van:

IF(BTW = HOOG) Then 
btw_hoog_totaal = btw_hoog_totaal + (Range("x" & VolgNummer") * btw_tarief_hoog) 'Als huidige cel hoge btw heeft dan het hoog btw bedrag uitrekenen en dit bij het totale hoog btw bedrag optellen.

Als ik nu de laatste drie rijen verwijder dan gaat deze berekening fout, mijn variabele VolgNummer ziet immers niet dat de laatste 3 rijen weg zijn. Ik zou dan het aantal verwijderde rijen van VolgNummer af moeten trekken.

Mochten er betere/mooiere oplossingen zijn voor dit vraagstuk dan hoor ik dit graag. Ik ben geen geweldig programmeur in Excel maar ik leer graag bij!

Edit: Ik heb het inmiddels werkend, wel met behulp van de extra kolommen. Nu bezig met de volgende uitdaging. Een "leeg" excel bestand kopiëren, opslaan onder een andere naam en dan de lijst met producten hierin kopiëren.

Bekijk bijlage Test bestand.xls

 

[Basto]

Wederom weer wat verder met het bestand. De btw en kostenberekening lijkt allemaal te werken zoals gewenst. Nu kan ik in dit bestand dus min of meer een factuur opmaken (adres, producten, aantallen, etc). Ik heb een knop toegevoegd "Factuur opslaan".
Ik heb in een map op mijn computer een template geplaatst van een lege factuur. Als ik op de knop "Factuur opslaan" klik wil ik dat dit template wordt gekopieerd, hernoemd en dan wordt vol geschreven met de informatie in mijn bestand. Tevens wil ik dat een deel van de informatie naar een ander excel bestand "Boekhouding" wordt weg geschreven. Het gaat dan om totaalbedrag, factuurnummer, etc.

Maar goed, één ding tegelijk. Mijn factuurnummers zijn als volgt ingedeeld "ABC-01 201601001"

• ABC-01 is de afkorting van een klant
• 2016 is het jaar
• 01 is de maand
• 001 is het nummer van de factuur in die maand

Ik heb mijn macro nu zover dat hij het eerste deel aanmaakt, dus ABC-01 201601 maar nu moet daar het nummer van de factuur dus nog bij. Mijn macro zou dus in mijn map met facturen moeten kijken wat daar de nieuwste factuur is, bijvoorbeeld xyz-02 201604012, oftewel factuur 12 van april. Als de volgende factuur ook in april is dan wordt het dus....013. Is de volgende factuur in mei dan wordt het ...201605001.
Mijn macro zal dus moet controleren of de huidige maand hetzelfde is als de maand van de laatst opgeslagen factuur, zo ja, tel 1 bij het laatste nummer op. Zo nee, pas de maand aan en begin bij 1.
Ik vermoed dat ik het controle en aanpas deel wel voor elkaar krijg. Mijn probleem is vooral dat ik niet weet hoe ik het nummer van de laatst aangemaakte factuur kan "opvragen" en in een variabele kan zetten.

Iemand die me hier bij wil helpen?

Als bijlage de meest recente versie van mijn bestand.

Edit: op google heb ik een functie gevonden die de naam van het meest recente bestand weergeeft, dit werkt. Nu moet ik nog uitvogelen hoe ik een controle functie maak die alleen naar de laatste karakters kijkt van een naam.
Edit2: met wat links en rechts schuiven lijkt ook dit gelukt.

Bekijk bijlage Test bestand.xls

 

[VenA]

Dat gaat dan een hele uitdaging worden. Ik heb nog nooit een factuurnummer gezien met als prefix een gedeelte van de naam van de klant. Verder vind ik een prefix met jaar en maand ook niet nodig. Een factuurnummer moet uniek zijn dus alleen een uniek nummer is voldoende. Maar dat is mijn mening:d

Maak een tabje aan waarin je de factuurnummers bijhoudt.

Zoiets kan dan unieke nummers aanmaken.

Sub VenA()
Prefix = Format(Date, "yyyymm")
With Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
 .NumberFormat = "@"
 .Value = Prefix & Right("000000" & .Row - 1, 6)
End With
End Sub

 

[Basto]

Je hebt gelijk, ik had het misschien iets beter moeten omschrijven. Op de factuur staat een nummer in de vorm: 201601001. Ik sla deze echter op met een voorvoegsel in de vorm van een afkorting voor de klant. Bijvoorbeeld JAN-01 201601001.
Bijvoorbeeld de klant "Jansen" en dit was de eerste Jansen. Als ik meerdere Jansens heb dan wordt het JAN-02.

Het was even wat gepriegel met if lussen maar de functie lijkt nu te werken. Hij kijkt naar de vorige factuur en controleert eerst of de maand van die factuur gelijk is aan de huidige maand. Zo nee, dan beginnen we bij factuurnummer 001.
Zo ja, dan kijkt hij naar het factuurnummer van de laatste factuur en telt hier 1 bij op.

Ik ben het met je eens dat het allemaal niet op deze manier hoeft. Ik heb de factuurnamen al jaren op deze manier dus als het kan wil ik dit zo voortzetten. En dit blijkt dus te kunnen, met wat gepriegel. Nu op naar de volgende uitdaging. Het kopiëren van de producten uit het hoofdbestand naar de nieuw aangemaakte factuur. Bijkomende uitdaging is dat ik 30 regels ter beschikking heb. Als ik dus meer producten per factuur wil maken zal mijn macro automatisch een pagina einde moeten invoeren en dergelijke.
Maar goed, we gaan stapje voor stapje verder en kijken wel wat we tegen komen.

Nogmaals dank voor de hulp zo ver, en ik vermoed dat er nog wel wat vraagstukken langs zullen komen

 

[Basto]

Ik ben inmiddels weer een stukje verder. Het kopiëren van de aangemaakt producten naar de factuur lukt. Deze wordt nu automatisch opgeslagen en geprint. Ik krijg het echter nog niet voor elkaar om bij een te lange rij met producten over te stappen naar een tweede blad in de factuur. Ik zoek in excel een functie om te kijken of het resultaat van een formule een heel getal is.

In de factuur heb ik plaatst voor 30 regels. Als ik 31 regels heb dan moet ik dus een tweede blad aanmaken. In pseudo code:

Als (benodigde rijen / beschikbare rijen) > even getal dan
aanmaken tweede blad en daar de rest van de artikelen opzetten

Ik kan in excel zo geen functie vinden die deze controle voor mij doet. Anders kon ik namelijk een macro opnemen en kijken wat daar voor code uitrolt. Is deze functie beschikbaar in excel?

Edit: het lijkt er op dat ik nu kan bepalen hoeveel factuurbladen ik nodig heb. Althans, ik weet hoe ik dit wil doen. Om een benodidge waarde te bepalen heb ik de onderstaande code:

Do Until (IsError(Range("D" & invul_rij)) Or IsEmpty(Range("D" & invul_rij))) 'voor onderstaande code uit totdat je in kolom D een #N/B of lege cel tegenkomt
    aantal_rijen = aantal_rijen + 1
Loop
MsgBox aantal_rijen

Bij het uitvoeren van deze loop loopt mijn excel vast en ik kan nog niet achterhalen wat ik fout doe.

De DO functie kijkt of hij in een range een leeg vak of een vak met #N/B (of #N/A) tegen komt. Hij doorloopt deze loop net zo lang totdat dit het geval is. De DO loop werkt want ik heb hem werkend met een andere functie in de lus. Dat was mijn eerste opzet maar in die opzet kon ik niet vooraf bepalen hoeveel pagina's ik nodig had.

Edit: Na een nachtje slaap zag ik dat het ook wel slim is om invul_rij telkens met 1 te verhogen... Op de manier zoals het er nu staat kijkt de loop constant naar dezelfde cel.