variabel bereik in cellen met vaste opmaak

[Basedude]

Volgend probleempje:

Ik heb, even een simpel voorbeeld, uit een datbase de volgende gegevens gefilterd:

A
1
2
B

A
1
2
3
B

A
1
B

Dus de reeksen beginnen altijd met een A en eindigen altijd met een B en de hoeveelheid gegevens daar tussenin is dus sterk variabel. Nu wil ik deze reeksen in een blad zetten met vaste opmaak van bij voorbeeld standaard 7 regels voor elke reeks. Hoe kan ik nu de brongegevens uit de database automatisch in mijn sheet kopieren zodat in mijn voorbeeld het volgende resultaat volgt:

regel reeks
1 A
2 1
3 2
4 B
5
6
7
8 A
9 1
10 2
11 3
12 B
13
14
15 A

etc etc.

Basedude

 

[jzeedijk]

Hoi Basedude,

De eenvoudigste oplossing lijkt op die van Amadeus. In blad1 zet je in kolom K de formule:
=als(A11="A";RIJ();"")

En kopieer deze verticaal.

Op blad2 in kolom K de formule:
=als(rest(RIJEN(K$1:K1)-1;7)=0;als(aantal(Blad1!K:K)>(RIJEN(K$1:K1)-1)/7+1;"";KLeinste(BLAD1!K:K;(RIJEN(K$1:K1)-1)*7+1));Formule)
In rij 1 formule=1 weglaten, vanaf rij2 formule=K1+1

Vervolgens in kolom A de index-formule:
=als($K1>=Min($K1:$K$9999);"";als(Index(Blad1!A:A;$K1)="";"";Index(Blad1!A:A;$K1)))

Jeroen

 

[Basedude]

Jeroen,

Bedankt voor je oplossing maar ik geloof niet dat ik het helemaal begrijp. Als ik de formules invoer krijg ik steeds een foutmelding. Ik heb voor de duidelijkheid een voorbeeldbestandje bijgevoegd waardoor het visueel ook wat duidelijk wordt. Ik zou dus graag zien dat de brongegevens door bijvoorbeeld een macro in het basisbestand gegoten worden. In mijn voorbeeld heb ik een aantal ritten er handmatig ingezet, in werkelijkheid zijn het tientallen ritten. Vandaar mijn vraag voor een automatische oplossing. Hopelijk kan iemand mij helpen. Ik zat zelf aan iets met regels tellen vanaf Aankomst tot Vertrek te denken maar ik kom er maar niet uit.

Basedude

 

[richard1970]

Hoi Basedude,

Als je zeker weet dat er nooit meer dan 7 rijen zitten tussen twee opeenvolgende ritten, dan kan onderstaande macro helpen. 't Zit vol met loops en dat is niet bepaald mijn sterkste punt in VBA, maar het werkt.

Start VBA via Alt+F11, en klik links in de Projectverkenner op ThisWorkbook. Kopieer in het rechterscherm het volgende:

Sub Invoegen_rijen()
Dim BeginRij
Dim EindRij
Dim Afstand
Dim Invoegen
Range("A5").Select
BeginRij = 5
Selection.End(xlDown).Select
Do Until ActiveCell.Row = 65536
EindRij = ActiveCell.Row
Afstand = EindRij - BeginRij
Do Until Afstand < 7
If Afstand >= 7 Then
Afstand = Afstand - 7
Else
Do Until Invoegen = 0
Invoegen = Afstand
Selection.EntireRow.Insert
Invoegen = Invoegen - 1
Loop
End If
Loop
Invoegen = 7 - Afstand
Do Until Invoegen = 0
Selection.EntireRow.Insert
Invoegen = Invoegen - 1
Loop
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Loop
Range("A1").Select
End Sub

Als je nu in het blad waarin de ruwe gegevens staan op Alt+F8 klikt en dan de macro Invoegen_rijen start, dan staat in een mum van tijd alles goed. Als ik tenminste overal aan gedacht heb.

In woorden: Ik bepaal het afstand tussen de ene rit (Beginrij) en de volgende rit (eindrij). Als de afstand (eindrij - beginrij) vijf rijen is, dan moeten er nog 2 worden tussengevoegd.

Ik hoop overigens wel dat een ander met meer verstand van VBA dan ik er iets mooiers van kan maken :thumb:

Succes,

Richard

 

[Basedude]

Richard,

Mijn dank is groot !!!!!!!!!!! :thumb:

Basedude

 

[jzeedijk]

Hoi Basedude,

De excel-oplossing, met wat kleine aanpassingen.

Jeroen