Formule doortrekken naar laatste cel

[madmad]

L.s.

Eind maart heb ik de volgende vraag geplaatst over het doortrekken van een formule in een macro:
Wat ik graag wil is het volgende:
in de eerste kolom staat een reeks getallen, in de tweede kolom wil ik een formule plaatsen die doorgetrokken wordt tot de laatste actieve rij. Soms zijn dit 10 rijen, soms zijn het 50.000 rijen. Hoe kan ik ervoor zorgen dat de formule wordt doorgetrokken tot de laatst actieve rij?

Het antwoord dat ik ontving leek goed maar nu kom ik er niet verder mee in de volgende macro:

Sub omzetten_export()
'
' omzetten_export Macro
' omzetten export naar Key2
'
' Sneltoets: Ctrl+q
'
Range("J1").Select
ActiveCell.FormulaR1C1 = "bedrijfsnummer"
Range("K1").Select
ActiveCell.FormulaR1C1 = "grootboeknummer"
Range("L1").Select
ActiveCell.FormulaR1C1 = "lege kolom1"
Range("M1").Select
ActiveCell.FormulaR1C1 = "lege kolom2"
Range("N1").Select
ActiveCell.FormulaR1C1 = "lege kolom3"
Range("O1").Select
ActiveCell.FormulaR1C1 = "ECL"
Range("P1").Select
ActiveCell.FormulaR1C1 = "i/u"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "bedrag DEBet"
Range("R1").Select
ActiveCell.FormulaR1C1 = "bedrag Credit"
Range("S1").Select
ActiveCell.FormulaR1C1 = "lege kolom4"
Range("T1").Select
ActiveCell.FormulaR1C1 = "lege kolom5"
Range("U1").Select
ActiveCell.FormulaR1C1 = "omschrijving"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]="" "","" "",10)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J10")
Range("J2:J10").Select
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]="""","""",RC[-5])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K10")
Range("K2:K10").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]="""","""",RC[-8])"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O10")
Range("O2:O10").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]="""","""",(LEFT(RC[-8],5)))"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O10")
Range("O2:O10").Select
Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]="""","""",(RIGHT(RC[-9],1)))"
Range("P2").Select
Selection.AutoFill Destination:=Range("P210")
Range("P210").Select
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=0,"""",RC[-9])"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q10")
Range("Q2:Q10").Select
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]="""","""",RC[-9])"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=0,"""",RC[-9])"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R10")
Range("R2:R10").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-20]="""","""",(CONCATENATE(RC[-17],"" "",RC[-16])))"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U10")
Range("U2:U10").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub



In bovenstaand geval heb ik de macro opgenomen in een bestand waarin 10 rijen stonden. Maar de macro moet de ene keer worden uitgevoerd in een bestand met 100 regels en een andere keer in een bestand met 100.000 regels.

Wie kan mij helpen?

Met groeten,
Maurice

 

[snb]

Zet svp eerst de VBA-code in je bericht tussen code-markeringen (Code tags).

Vereenvoudig vervolgens je code zodat alle 'selects' en 'activate's verwijderd zijn.

 

[Gijsbert1]

Eerst eens uitvoeren wat SNB zegt inderdaad.
Maar als opstapje, zo zou je de Macro kunnen starten:

Sub omzetten_export()
'
' omzetten_export Macro
' omzetten export naar Key2
'
' Sneltoets: Ctrl+q
'

Dim LaatsteRij As Long
LaatsteRij = Range("A" & Rows.Count).End(xlUp).Row

Range("J1").FormulaR1C1 = "bedrijfsnummer"
Range("K1").FormulaR1C1 = "grootboeknummer"
Range("L1").FormulaR1C1 = "lege kolom1"
Range("M1").FormulaR1C1 = "lege kolom2"
Range("N1").FormulaR1C1 = "lege kolom3"
Range("O1").FormulaR1C1 = "ECL"
Range("P1").FormulaR1C1 = "i/u"
Range("Q1").FormulaR1C1 = "bedrag DEBet"
Range("R1").FormulaR1C1 = "bedrag Credit"
Range("S1").FormulaR1C1 = "lege kolom4"
Range("T1").FormulaR1C1 = "lege kolom5"
Range("U1").FormulaR1C1 = "omschrijving"

Range("J2").FormulaR1C1 = "=IF(RC[-9]="" "","" "",10)"
Range("J2").AutoFill Destination:=Range("J2:J" & LaatsteRij)

Range("K2").FormulaR1C1 = "=IF(RC[-5]="""","""",RC[-5])"
Range("K2").AutoFill Destination:=Range("K2:K" & LaatsteRij)

De rest moet zelf wel lukken, toch?

 

[edmoor]

Het eerste gedeelte kan zelfs zo:

    Range("J1") = "bedrijfsnummer"
    Range("K1") = "grootboeknummer"
    Range("L1") = "lege kolom1"
    Range("M1") = "lege kolom2"
    Range("N1") = "lege kolom3"
    Range("O1") = "ECL"
    Range("P1") = "i/u"
    Range("Q1") = "bedrag DEBet"
    Range("R1") = "bedrag Credit"
    Range("S1") = "lege kolom4"
    Range("T1") = "lege kolom5"
    Range("U1") = "omschrijving"
    Columns.AutoFit

 

[Gijsbert1]

Inderdaad.

En het 2e stukje is autofill niet nodig als je dit:

Range("J2").FormulaR1C1 = "=IF(RC[-9]="" "","" "",10)"
Range("J2").AutoFill Destination:=Range("J2:J" & LaatsteRij)

Range("K2").FormulaR1C1 = "=IF(RC[-5]="""","""",RC[-5])"
Range("K2").AutoFill Destination:=Range("K2:K" & LaatsteRij)

Vervangt door dit:

Range("J2:J" & LaatsteRij).FormulaR1C1 = "=IF(RC[-9]="" "","" "",10)"
Range("K2:K" & LaatsteRij).FormulaR1C1 = "=IF(RC[-5]="""","""",RC[-5])"

 

[snb]

of liever met:

Range("J1:U1") = array("bedrijfsnummer","grootboeknummer","lege kolom1","lege kolom2","lege kolom3","ECL","i/u","bedrag DEBet","bedrag Credit","lege kolom4","lege kolom5","omschrijving")

 

[Gijsbert1]

:d Van 24 regels naar 1 regel... :thumb: