[VBA] Slepen van formule tot laatste cel in bereik

[Aristos]

Ik heb in mijn Excel twee kolommen. Kolom B maakt een functie aan en die trek ik tot nu toe handmatig door tot het laatste getal in kolom A. Dit wil ik dmv een macro automatiseren. Als ik tijdens het maken van de macro het handmatig doe, krijg ik deze code:

Selection.AutoFill Destination:=Range("B2:B10000"), Type:=xlFillDefault
Range("B2:B10000").Select

Die B10000 moet dus afhankelijk zijn van de vulling in kolom A. Ik heb geen kennis van VBA en heb gezocht op GoT maar daar komen alleen antwoorden in voor waarin van te voren al is bepaald tot waar kolom B gevuld moet gaan worden. Die waarde heb ik niet.

Omdat ik nog geen verstand van VBA heb, zou ik het op prijs stellen als de tip/oplossing ook in Jip en Janneke taal gegeven wordt. Dan kan ik het hopelijk de volgende keer zelf...

Nog excuses voor het vorige topic, momentje van verstandsverbijstering...

Oke, het is nog vroeg, nog een momentje van verstandverbijstering... Topic titel... Maak er maar [VBA] Doortrekken van formule tot laatste cel in macro

[ Voor 10% gewijzigd door Aristos op 21-04-2006 10:33 . Reden: topictitel vergeten ]

[Lustucru]

Ok, jip en janneke en didactisch:

Wat je zoekt is dus tweeledig:
1: Hoe bepaal ik de grootte van het bereik van in kolom A:A
2: Hoe verwerk ik die gegevens in een formule.

1: [rml]KingRichard in "[ vba] Range definieren"[/rml]

code:

rmax=Range("A:A").SpecialCells(xlCellTypeLastCell).Row
of
set laatstecel=Range("A:A").SpecialCells(xlCellTypeLastCell)

plaatst het rijnummer van de laatst gebruikte cel in de variabele rmax resp de cel zelf in de variabele laatstecel

2: Dat kan op duizend en een manieren. Op een of andere manier moet er een bereik gedefinieerd worden in kolom B van rij 2 tot en met rij Rmax.

code:

range("B2:B" & rmax)
range(cells(2,2),cells(rmax,2))
range(cells(2,2),laatstecel.offset(0,1))
range("B2").resize(rmax-1)

komt allemaal op hetzelfde neer: het levert een bereik op van B2:Brmax.

1+2=3:

code:

rmax=Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Selection.AutoFill Destination:=range("B2").resize(rmax-1), Type:=xlFillDefault

Voor een toelichting op de gebruikte technieken: zie F1

Zoals gezegd heb ik nul komma nul verstand van VBA, als ik de code bekijk snap ik hem ook niet helemaal. Maar de code van King Richard werkt prima, topic kan dicht

offtopic:
daar zit de makke, imho. Zo moeilijk was dat stukje code van KR nu ook weer niet. VBA leer je het beste door te proberen te begrijpen wat er gebeurt, en niet te makkelijk knippen en plakken

[ Voor 5% gewijzigd door Lustucru op 21-04-2006 11:19 ]

[Verwijderd]

de specialcellseigenschap is niet onfeilbaar. maar als ik het goed begrijp kan je dit gewoon handmatig dmv een dubbelklik op de rechteronderhoek van de cel die de oorspronkelijke formule bevat. wil je toch code, probeer dan eens (niet getest)

Visual Basic:

Selection.AutoFill Destination:=Range(range("B2"), range("a65536").end(xlup).offset(0,1)), Type:=xlFillDefault

[Lustucru]

Verwijderd schreef op vrijdag 21 april 2006 @ 17:21:
de specialcellseigenschap is niet onfeilbaar.

dat wist ik maar aan dit

code:

range("a65536").end(xlup)

had ik -eerlijk gezegd- nog nooit gedacht.

nu werk ik ook bijna nooit met Excel, maar toch...

[ Voor 3% gewijzigd door Lustucru op 21-04-2006 19:49 ]

[Cheezus]

Even een topic-kick:

Ik zit met hetzelfde probleem als de TS, alleen heb ik 2 werkbladen. Op werkblad 2 importeer ik een lijst uit een bestand en op werkblad 1 wil ik de totalen van deze lijst.

Werkblad 2 ziet er ongeveer zo uit:
Tekst A xxx
Tekst B xxx
Tekst C xxx
Tekst A xxx
Tekst C xxx
enz.

Op werkblad 1 wil ik dus alleen de unieke waardes uit het 2e werkblad (kolom 1) en daarachter de totalen opgeteld:
Tekst A xxxxx
Tekst B xxxxx
Tekst C xxxxx

De formule om de totalen bij elkaar op te tellen moet dus doorgetrokken worden zover als er unieke waardes zijn.

code:

rmax=Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Selection.AutoFill Destination:=range("B2").resize(rmax-1), Type:=xlFillDefault

Met deze code krijg ik echter het aantal rijen die op werkblad 2 gebruikt worden, wat er dus veel te veel zijn.
Ik krijg het niet voor elkaar om het aantal gebruikte rijen van werkblad 1 in de autofill te krijgen.

[Jarcol]

primusz schreef op maandag 03 september 2007 @ 13:27:
Even een topic-kick:

Ik zit met hetzelfde probleem als de TS, alleen heb ik 2 werkbladen. Op werkblad 2 importeer ik een lijst uit een bestand en op werkblad 1 wil ik de totalen van deze lijst.

Werkblad 2 ziet er ongeveer zo uit:
Tekst A xxx
Tekst B xxx
Tekst C xxx
Tekst A xxx
Tekst C xxx
enz.

Op werkblad 1 wil ik dus alleen de unieke waardes uit het 2e werkblad (kolom 1) en daarachter de totalen opgeteld:
Tekst A xxxxx
Tekst B xxxxx
Tekst C xxxxx

De formule om de totalen bij elkaar op te tellen moet dus doorgetrokken worden zover als er unieke waardes zijn.

code:

rmax=Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Selection.AutoFill Destination:=range("B2").resize(rmax-1), Type:=xlFillDefault

Met deze code krijg ik echter het aantal rijen die op werkblad 2 gebruikt worden, wat er dus veel te veel zijn.
Ik krijg het niet voor elkaar om het aantal gebruikte rijen van werkblad 1 in de autofill te krijgen.

En zo?

Visual Basic:

rmax=Worksheets("Werkblad 1").Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Selection.AutoFill Destination:=range("B2").resize(rmax-1), Type:=xlFillDefault 

[Cheezus]

Dat geeft helaas hetzelfde resultaat.
Ik heb inmiddels 'vals gespeeld' en het zo opgelost:

code:

Range("E1").FormulaR1C1 = "=COUNTA(C[-4])"
    Range("E1").Copy
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      
    Worksheets("Rapport").Range("E3").FormulaR1C1 = "=SUMIF(Data!C[-4],C[-4],Data!C[-3])"
    Worksheets("Rapport").Range("E3").AutoFill Destination:=Range("E3").Resize(Worksheets("Rapport").Range("E1") - 2), Type:=xlFillDefault

Oftewel, ik tel eerst het aantal cellen in kolom A, vervolgens copy-paste ik de waarde daarvan in dezelfde cel.
Deze waarde gebruik ik vervolgens i.p.v. rmax

[Verwijderd]

niet getest:

code:

range(worksheets("rapport").range("e3"),worksheets("rapport").range("a65536").end(xlup).offset(4)).formular1c1="=SUMIF(Data!C[-4],C[-4],Data!C[-3])"

[Cheezus]

Daarmee krijg ik wel het juiste aantal rijen alleen plakt hij de formule in alle cellen van A t/m E.

Ik zal er nog eens verder naar kijken want mijn methode gaat een beetje de mist in als ik maar 1 unieke waarde heb in de lijst.

[Verwijderd]

toch maar beter eerst wel testen beste _heretic_

code:

range(worksheets("rapport").range("e3"),worksheets("rapport").range("a65536").end(xlup).offset(0,4)).formular1c1="=SUMIF(Data!C[-4],C[-4],Data!C[-3])"

[Cheezus]

Nu werkt het perfect, mijn dank is groot

[Marcel_67]

Ik weet dit is een behoorlijk oud topic maar toch wil ik het proberen

Ik zit namelijk met een gelijkwardig probleem als topic starter, echter wil ik over meerdere bladen de formules in alle kollommen doortrekken tot de laatste waarde. Het makkelijkste is natuurlijk om alle tabs om te zetten naar tabel, maar dan wordt het bestand te zwaar voor de meeste gebruikers.

Ik had een macro opgenomen, zie onder, maar ik heb het idee dat het makkelijker moet kunnen. Zou iemand mij hierin kunnen ondersteunen ?
Gelieve het in jip en janneke taal uit te leggen aangezien ik nog niet zo lang met VBA werk


Sub Formule()
' formules doortrekken

'ZMMDR01
Sheets("ZMMDR01").Select
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E95000")
Range("E2:E95000").Select
'LS11
Sheets("LS11").Select
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T10000")
Range("T2:T10000").Select
'Artspecs
Sheets("Artspecs").Select
Range("V2:X2").Select
Selection.AutoFill Destination:=Range("V2:X9000")
Range("V2:X9000").Select
'LX03
Sheets("Lx03").Select
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T10000")
Range("T2:T10000").Select
'LX29
Sheets("LX29").Select
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N7000")
Range("N2:N7000").Select
'3e laadeenheid
Sheets("3e").Select
Range("B3:AG3").Select
Selection.AutoFill Destination:=Range("B3:AG7000")
Range("B3:AG7000").Select
'Totaal
Sheets("Totaal").Select
Range("B2:X2").Select
Selection.AutoFill Destination:=Range("B2:X10000")
Range("B2:X10000").Select
'Bezetting
Sheets("Bezetting").Select
ActiveWorkbook.RefreshAll
End Sub

[dix-neuf]

Hoe "Autofill" werkt (of "Copy", dat kan ook), dat weet je inmiddels (van hierboven of uit de helpfile van Excel).
Als je die methode(n) over meerdere bladen wil laten lopen, dan hoef je er enkel een for-nextlus rond te plaatsen, waarmee je alle gewenste bladen doorloopt.

[Lustucru]

Hoe wil je er een for-next lus omheen hangen als bij elk blad een ander deel moet worden doorgetrokken? De enige optimalisatie die ik zo 1-2-3 zie is het weghalen van alle overbodige selects icm shorthand syntax. Je houdt dan per aktie slechts één regel over. Bv de eerste vier regels kun je vervangen door:

code:

Sheets("ZMMDR01").[E2].AutoFill Sheets("ZMMDR01").[E2:E95000]

[dix-neuf]

Lustucru schreef op vrijdag 1 mei 2020 @ 18:47:
Hoe wil je er een for-next lus omheen hangen als bij elk blad een ander deel moet worden doorgetrokken

Tja, je mag toch veronderstellen dat de vragensteller een beetje meedenkt en wel inziet dat hij het bereik in alle bladen hetzelfde moet maken Maar ook als dat niet kan, dan kun je, als je voor rij en kolom een variabele instelt, het bereik in de for-nextlus per blad nog wel aanpassen (bv., als r en k variabelen zijn voor rij en kolom: r= r+2 en k=k-3).