Autofill dynamisch uitvoeren

[gonzo31]

hallo allen,

Ik ben bezig met een stukje VBA. Het hele script draait, op een puntje na.
Het script voegt nieuwe regels toe in een tabel. Echter is Excel wel eens eigenwijs en daardoor worden de formules in de tabel in de ene kolom wel automatisch aangevuld in de nieuwe regels, maar in de andere kolom (later toegevoegd) weigert Excel medewerking te verlenen. Op zich geen probleem, dan maak ik wel een stukje aan het VBA script vast, was mij idee. Maar nu loop ik daar toch op vast.

Ik heb het volgende (deel van het) script:

    [N65536].End(xlUp).Offset(-iTijd, 0).Activate
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(iRegels, ActiveCell.Column)), Type:=xlFillDefault

Hierin wordt iRegels eerder gedefinieerd als een getal, laten we even als voorbeeld 30 nemen.
Ook iTijd wordt correct gedefinieerd. De juiste cel word namelijk geselecteerd en in deze cel bevind zich een formule, laten we even zeggen dat die formule inhoudt "de waarde op dezelfde rij in kolom M - de waarde op dezelfde rij in kolom L".

De bedoeling van het VBA script is het autofill-en van de nieuw toegevoegde regels.
Wat er echter gebeurd is dat vanaf de 'activeCell' de hele kolom naar boven toe wordt gevuld t/m 30 regels (iRegels) van boven.
Ik moet juist hebben dat de autoFill vanaf de 'activeCell' het aantal van iRegels naar beneden aanvult.

Kan iemand mij hier mee helpen? Ik heb al allerlei formaten geprobeerd maar het loopt telkens spaak.
Het is vast simpeler dan ik denk en ik zal mezelf wel voor m'n hoofd kunnen slaan, maar goed. Alle feedback is welkom!

Bedankt!
Arjan

 

[gonzo31]

Voor de info:
- Het betreft hier Excel 2013
- Het bereik van iRegels mag ook gewoon einde van de tabel zijn (xlUp van de kolom links van kolom N)

 

[MirjamHelp]

Hoi Arjan,

Zoals ik het lees:
Stel dat actieve cel is ingesteld op een cel in rij 35 en jij wil naar een cel in rij iRegels(=30 in het voorbeeld), dan ga je dus omhoog.
Stel dat actieve cel in ingesteld op een cel in rij 25 en jij wil naar een cel in rij iRegels(=30 in het voorbeeld), dan ga je dus omlaag.

Ik heb verder geen zicht op je precies in je werkblad hebt staan of wat er tijdens de procedure gebeurt, maar misschien helpt dit je op weg.

Gr. Mirjam

 

[gonzo31]

Hoi Mirjam,

Bedankt dat je naar m'n vraag wilde kijken.
Wat jij zegt is niet helemaal wat ik bedoel. Ik probeer het even te verduidelijken.
- Ik heb een tabel bestaande uit gegevens in kolom A tm N.
- Kolom N is een berekening die gemaakt wordt op kolom L en M.
- Door middel van een macro voeg ik in één keer een x aantal regels toe en die X is ter grootte van iRegels.

Nu loopt in mijn tabel de berekening van kolom N niet automatisch door (auto aanvullen). Wat ik dus doe met de code zoals hier boven is eerst de laatste cel uit kolom N zoeken waar wel de berekening in staat (dat is dus de laatste regel die ik heb toegevoegd, de vorige keer dat ik de macro uitvoerde) en dan maak ik die cel activeCell zodat ik vanuit daar een autoFill kan maken om de nieuw toegevoegde regels te voorzien van de berekening.

M.a.w. In kolom A tm N en tm regels 266 staan al gegevens (compleet willekeurig regelnummer) en ik voeg nu 30 regels toe d.m.v. de macro. In de hele tabel loopt autoFill mee behalve in kolom N dus wil ik de macro de autoFill laten doen. Daarom zet ik cel N266 active en dan moet er dus een autoFill komen voor de 30 volgende regels (tm N296 in dit voorbeeld). Aangezien die 30 variabel is, heb ik die gedeclareerd als in integer; iRegels.

De tabel groeit dus elke keer dat ik de macro uitvoer een stukje langer. Ook schuift dus de laatste cell met een berekening op naar kolom N en de nieuwe, dan laatste regel. Althans, als die macro dus goed werkt en dat doet ie niet.

Wellicht denk ik te moeilijk. Ik zit te puzzelen met het feit dat die autoFill ter grootte van iRegels moet zijn. Maar, ik zou ook kunnen zeggen: vul maar aan tot het einde van de tabel. Alleen krijg ik ook dit stukje vba niet voor elkaar

Hoop dat het zo duidelijker is.

Gr. Arjan

 

[MirjamHelp]

Hallo Arjan,

Helemaal duidelijk nu, goed omschreven!

Aanvullende declaratie

Dim lngLaatsteRij As Long

die vul je door

lngLaatsteRij = Range("N" & Application.Rows.Count).End(xlUp).Row

En vervolgens met de AutoFill methode

Range("N" & lngLaatsteRij).AutoFill Destination:=Range("N" & lngLaatsteRij & ":N" & lngLaatsteRij + iRegels), Type:=xlFillDefault

of de methode om een bereik met CTRL-ENTER te vullen

Range("N" & lngLaatsteRij & ":N" & lngLaatsteRij + iRegels).Formula = Range("N" & lngLaatsteRij).Formula

of wanneer je de cel kopieert, dan wordt de code

Range("N" & lngLaatsteRij).Copy Destination:=Range("N" & lngLaatsteRij & ":N" & lngLaatsteRij + iRegels)

Een van bovenstaande manieren is meestal zoals ik die gebruik, dus in combinatie met lngLaatsteRij.



Toch nog even naar jouw code kijken

ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(iRegels, ActiveCell.Column)), Type:=xlFillDefault

Als ik die vertaal naar gewone taal dan lees ik:
Autovullen vanaf Actieve cel naar het bereik van de actieve cel tot en met cel in rij=30 met kolom waarbij de kolom gelijk is aan die van de actieve cel
ofwel wanneer je actieve cel in rij 35 van kolom A staat wordt het doel-bereik: A35:A30, je gaat dus omhoog vanuit je actieve cel gezien
en wanneer je actieve cel in rij 25 van kolom A staat wordt het doel-bereik:A25:A30, dan ga je omlaag vanuit je actieve cel gezien
Je werkt met jouw code dus altijd vanuit rijnummer iRegels, dus eigenlijk vanuit het rijnummer dat gelijk is aan het aantal regels dat je toevoegt.
Om jouw code te corrigeren dien je daarom het rijnummer van de actieve cel op te tellen bij het aantal toe te voegen regels
dus

ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells([U][B]ActiveCell.Row + [/B][/U]iRegels, Activecell.Column)), Type:=xlFillDefault

Gr. Mirjam

 

[gonzo31]

Hoi Mirjam,

Ik zie nu waar ik de fout heb begaan. Ik verwees inderdaad naar rij 30!
Ondanks dat het wellicht mooier is om de laatste rij te definiëren, declareer ik iRegels al aan het begin van de macro als de waardes uit een ander werkblad moeten komen en heb daarom voor de laatste optie gekozen die je mij hebt aangedragen en het werkt perfect!

Bedankt voor je oplossing!
Arjan

 

[snb]

Echter is Excel wel eens eigenwijs en daardoor worden de formules in de tabel in de ene kolom wel automatisch aangevuld in de nieuwe regels, maar in de andere kolom (later toegevoegd) weigert Excel medewerking te verlenen.

Dat lijkt me stug.

In plaats van met VBA ingebouwde functionalitiet her uit te vinden zou ik de oorzaak in de tabel opsporen.
Toon een voorbeeldbestand en we helpen je daarbij.

 

[gonzo31]

Dat lijkt me stug.

Toch heb ik het meer gelezen op het internet. Er kwamen suggesties voorbij om in het Optie-menu van Excel 'Automatisch aanvullen' aan te vinken maar die staan gewoon aan. Ik heb het al eens eerder gehad dat ik een tabel aanmaakte, auto aanvullen werkt prima. Voeg je een kolom toe aan je tabel en hoppa, auto aanvullen doet het niet terwijl de kolom toch echt bij de tabel hoort! Erg raar :|

Voorbeeld bestandje gemaakt. Als je het kan vinden, des te beter, anders houd ik het bij de macro
Bekijk bijlage Voorbeeld.xlsm
Het wijst redelijk voor zich denk ik. In kolom A komt een dagnummer, kolom B een maand, kolom C een jaartal, in kolom D kun je een afkorting zoals een van de bovenstaande uit dezelfde kolom gebruiken, kolom E werkt het zelfde als D. Dan zijn F tm J verborgen een werkt auto aanvullen in K wel (ook in F tm J), dan bij L een tijd in vullen, bij M een tijd invullen en dan loopt kolom N niet door...

 

[snb]

Lijkt me inderdaad stug.

 

[gonzo31]

Haha cool, het wekt dus wel. Bedankt snb.

De grote vraag is en blijft nu: Hoe kan het dat het nu wel werk?

 

[snb]

Vergelijk het bestand dat jij plaatste met het bestand dat ik plaatste.

 

[gonzo31]

Verschillen die ik heb gevonden:
- extensie is .xlsb ipv xlsm
- verborgen kolommen F tm J zichtbaar gemaakt
- formule is veranderd (maar het werkt ook als ik die weer terug verander)
- in het lint onder 'start' staat de optie bij 'doorvoeren' op rijen i.p.v. kolommen

Maar het werkt nog steeds niet, tenzij ik een van jou cellen van kolom N naar mijn kolom N plak...

 

[gonzo31]

Kleine noot. De formule leek te werken maar verwees naar jou bestand, snb. Dus toch blijft nog de vraag, hoe moet ik het voor elkaar krijgen?
Alvast bedankt!

 

[snb]

De formule in N is =[@eind]-[@start]

Die verwijst niet naar een bestand maar naar kolommen van de tabel waarvan kolom N zelf ook deel uitmaakt.
Als je in jouw bestand alle gegevens en formules uit kolom N haalt en dan de formule in N2 zet met de correcte namen van de kolommen waarnaar de formule verwijst kan er niets mis gaan.
Anders moet je de fundamentals van tabellen eerst nog maar eens grondig en systematisch doornemen.

 

[gonzo31]

Ah het ging hier om het kleine detail eerst de kolom leeg te halen. Ik had er nog wat waarden in staan die ik handmatig in kolom N had toegevoegd.
Het feit dat ik een bestandsverwijzing kreeg kwam omdat ik de cellen kopieerde en in mijn eigen bestand plakte....

Dank voor de hulp.