cellen kopiëren naar een variabel aantal rijen

[HansAteB]

Ik ben me aan het bekwamen in vba.
Maar het volgende is me nog te hoog gegrepen.

Zie bijgaand bestand.

Na verwerken van een stuk vba (dat niet in het voorbeeld zit) staat de cursor in cel I2 (en er zijn veel kolommen verborgen).
Ik wil cellen AA2:AE2 (waarin formules staan) naar beneden kopiëren.
Het aantal regels naar beneden is afhankelijk van het aantal rijen dat bepaald wordt door de laatst gevulde cel in kolom A (tussendoor zijn er geen lege cellen).
Hoe los ik dit op met vba?
(voor mijn gevoel moet het met Offset en End(xlDown) of End(xlUp) (als je het vanonder af zou willen benaderen), maar bomen en bos).
Dank alvast voor een oplossing.

 

[snb]

In VBA:

- Kun je niet bewaren in een xlsx bestand (dat jij geplaatst heb)
- is de positie van een cursor niet relevant
- is het gebruik van Excel-formules in een werkblad meestal (> 99%) overbodig.
- is het verbergen van rijen of kolommen irrelevant: in VBA is alles verborgen.

 

[edmoor]

Laatste regel in kolom A bij alle cellen gevuld:

LaatsteRegelInKolomA = Range("A1").End(xlDown).Row

 

[HansAteB]

Dank voor reactie.

Punt 1 begrijp ik, maar de macro hiervoor zit in personal.xls.

Punten 2 tm 4 was me (nog) onbekend.


De gevraagde oplossing wil ik dus toevoegen aan de macro die al gebruikt wordt om het bestand te maken tot wat het nu is.
(Dat bestand kan ik vanwege privacy niet delen.)

Een oplossing (vba of macro-aanvulling) zou fijn zijn.

 

[HansAteB]

Laatste regel in kolom A bij alle cellen gevuld:

LaatsteRegelInKolomA = Range("A1").End(xlDown).Row

Dank.
Zo iets had ik verwacht.
Maar daarmee heb ik de cellen aa2:ae2 nog niet gekopieerd naar beneden tm die laatste regel.

 

[HansAteB]

Misschien goed om te delen wat ik al heb.

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1)) _
        , TrailingMinusNumbers:=True
    Range("I2").Select
    ActiveWindow.FreezePanes = True
    [G:H,AB:AB].NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    [AA:AA].NumberFormat = "[$-nl-NL]d/mmm/yy;@"
    [AA1].Value = "datum"
    [AB1].Value = "bedrag"
    [AE1].Value = "omschrijving"
    [AA2].FormulaR1C1 = "=RC[-22]"
    [AB2].FormulaR1C1 = "=-RC[-21]"
    [AC2,AD2].Value = " "
    [AE2].FormulaR1C1 = "=CONCATENATE(RC[-21],""__"",RC[-12],RC[-11])"
    [AA2:AE2].Copy [AA3:AA300]
    [A1:AE1].AutoFilter
    [A:AE].Columns.AutoFit
    [B:C,F:F,J:Z,AC:AD].EntireColumn.Hidden = True

De regel met Copy is een minder chique tussenoplossing. Het antwoord op mijn vraag zou hier moeten komen te staan.

 

[snb]

Je kunt je bestand altijd anonimiseren en hier plaatsen. Kijk eens wat verder in dit subforum voor voorbeelden.

Als ik je code zie dan is die volledig overbodig als je de import van het tekstbestand volledig met VBA afhandelt. Dat gaat ook nog eens veel sneller.

 

[HansAteB]

Zoals gezegd zit de macro in personal.xls omdat er steeds door derden aangeleverde csv's mee bewerkt moeten worden voor verdere verwerking.

Maar goed, bijgaand het bestand opnieuw, maar nu met macro erin.
De regel met Copy erin zou ik dus graag willen aanpassen zodat gekopieerd wordt tm de laatst gevulde regel.


Naar ik lees zou het met volledig vba veel efficiënter en sneller kunnen.
En dat geloof ik graag.
Maar dit zijn mijn eerste stappen om me echt in macro's/vba te bekwamen.
Ik hoop ooit zo ver te zijn als jullie.

Hoe ik mijn huidige code volledig in vba vorm moet geven is me nog volstrekt onduidelijk (en dat hoop ik te leren), maar ondertussen moeten aangeleverde csv's wel bewerkt worden.

 

[snb]

Plaats dan eens zo'n geanonimiseerd CSV bestand en geef in het Excelbestand met de hand aan hoe het resultaat eruit moet zien.

 

[HansAteB]

Dat is helaas niet te doen, want elke regel bevat meerdere privacy-gevoelige items.
Ik zal in het excel-bestand de kopregel plus 2 anders gevulde regels weergeven.
Als je dan de macro draait zie je wat de bedoeling is.
Alleen het kopiëren van aa2:ae2 moet dus anders.

Dank alvast voor het bekijken en je reactie.

 

[snb]

Waarom wil je gegevens 2 keer in 1 tabel ?
Maak van het tekstbestand een csv bestand: vervang de tabs door puntkomma. En dan gewoon openen in Excel.

 

[HansAteB]

Waarom wil je gegevens 2 keer in 1 tabel ?
Maak van het tekstbestand een csv bestand: vervang de tabs door puntkomma. En dan gewoon openen in Excel.

Het aangeleverde bestand is een csv-bestand met komma's.
Gegevens gaan door macro dubbel in kolom aa en ab en bewerkt in kolom ae.
Dit omdat kolommen aa:ae later in 1 kopieerverwerking in een ander bestand worden opgenomen, hetgeen anders drie kopieerslagen zou vergen (omdat de oorspronkelijke info niet naast elkaar staat).
Voor nu is het even niet anders, maar door betere kennis van macro/vba hopelijk ooit te verwerken op een veel slimmere manier.

 

[snb]

Het aangeleverde bestand is een csv-bestand met komma's.

Dan kun je het toch gewoon openen in Excel ?

Sub M_snb()
   workbooks.open "G:\OF\bestandmetkomma.csv"
end sub

 

[HansAteB]

Dan kun je het toch gewoon openen in Excel ?

Sub M_snb()
   workbooks.open "G:\OF\bestandmetkomma.csv"
end sub

In de 1e plaats is elk aangeleverd csv-bestand anders van naam (hierin vallen de mensen die aanleveren helaas nauwelijks op te voeden), dus een standaard opnemen in een macro werkt niet.
In de 2e plaats opent het csv-bestand in Excel met alleen maar ongesplitste rijen; de macro moet het opsplitsen naar kolommen.
En ten derde heb je dan ook nog de kolommen aa:ae niet gevuld.

 

[snb]

svp niet quoten; gebruik de blauwe knop.

pn de 2e plaats opent het csv-bestand in Excel met alleen maar ongesplitste rijen;

workbooks.open "G:\OF\voorbeeld.csv local:=True

 

[HansAteB]

Je gaat voorbij aan mijn punten 1 en 3.
En zoals gezegd geen vaste bestandsnaam, dus daar naar verwijzen heeft geen zin.

Er worden op jaarbasis minimaal 100 bestanden aangeleverd, waarvan misschien 10% een zelfde bestandsnaam hanteert.
Er zou meer werk in het hernoemen en verplaatsen van bestanden gaan zitten dan even de macro as-is te draaien.
En de kolommen aa:ae zijn juist nodig om extra opvolgend kopieerwerk te voorkomen, dus die moeten er door de macro ingezet worden.
Alleen dat gewenste naar beneden kopiëren zou beter kunnen; dat besef ik terdege.
Daarmee zal de gewenste macro/vba een efficiency-slag in de vervolgwerkzaamheden betekenen.

 

[cow18]

man, man, man.
Naar een onbekend bestand verwijs je gewoon met een getopenfilename en dan krijg je zo een keuzemenu.

    [AC2,AD2].Value = " "
     [AE2].FormulaR1C1 = "=CONCATENATE(RC[-21],""__"",RC[-12],RC[-11])"
   [COLOR="#FF0000"]  lastrow = ActiveSheet.UsedRange.Rows.Count
     [AA2:AE2].Copy [AA3].Resize(lastrow - 2)
[/COLOR]     [A1:AE1].AutoFilter

de rest lijkt me primitief.
zonder bepalen van de laatste rij, range("A1").end(xldown).offset(,26) of range("A" & rows.count).end(xlup).offset(,26) of nog een paar andere opties.
[] is old school, heeft een soort evaluate in zich, die soms ongewenst is, als je niet weet wat je doet.

 

[HansAteB]

Super. Dank je wel voor die code.

Je andere opties hoop ik binnenkort geleerd te hebben.
Zie in het daar genoemde nog niet hoe ik dat kan combineren met copy.
Maar we zullen zien.

[] heb ik gebaseerd op het verkorten (versnellen) van regels uit het boek van Wim de Groot.

 

[snb]

Gebruik VBA niet om Excelformules in een werkblad te zetten, maar gebruik Arrays.

 

[cow18]

     Range("AA2:AE2").Copy Range("AA3:AA" & Range("A" & Rows.Count).End(xlUp).Row)