Formule kopieeren met een variabele Range

[PaulMak]

Beste deskundigen,

Wie kan mij helpen met een kwestie waar ik na 2 weken uitproberen nog geen betrouwbare oplossing voor heb.

Ik werk dagelijks een bestand bij dmv een macro.
Het bestand is bevat meer dan 150.000 regels (dus meer dan de grens van 38.000)
Het bestand wordt dagelijks langer
De laatste kolom (AK) heeft wisselend lege en gevulde cellen
In kolom (B) zijn alle cellen gevuld

Kolom AL en AM worden aangevuld vanuit een 2e en 3e bestand dmv de formule vert.zoeken
Deze formule staat bovenaan de database en kopieer ik met een functie naar de laatste regel van het bestand.

Probleem: De kopieer functie hapert halverwege
Noodoplossing: Een range van 1.000.000 selecteren

Range("AL173736").Select
Selection.AutoFill Destination:=Range("AL173736:AL1000000")
Range("AL173736:AL1000000").Select

Range("AM173736").Select
Selection.AutoFill Destination:=Range("AM173736:AM1000000")
Range("AM173736:AM1000000").Select

Range("AL173737:AM173737").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Wie kan en wil mij helpen aan een nette oplossing.

Gr.
Paul

 

[oeldere]

Het bestand is bevat meer dan 150.000 regels (dus meer dan de grens van 38.000)

Deze regel snap ik niet, uitleg is gewenst.

Waarom niet een tabel gemaakt, dan wordt de formule automatisch doorgevoerd tot de laatste cel van de tabel.

 

[johhnnyboy]

Misschien eens een verkorte versie plaatsen met daarbij hoe het zou moeten werken? Wellicht is het in zijn geheel wat te vergemakkelijken.

 

[PaulMak]

Het bestand is bevat meer dan 150.000 regels (dus meer dan de grens van 38.000)

Deze regel snap ik niet, uitleg is gewenst.

Waarom niet een tabel gemaakt, dan wordt de formule automatisch doorgevoerd tot de laatste cel van de tabel.

Dank je wel voor jullie reacties.

Het gaat juist om het uitbreiden van een tabel ! De hoeveelheid regels van de tabel is echter zo groot dat de formule juist NIET automatisch doorgevoerd wordt.
Tevens is het automatisch doorvoeren van de formule niet betrouwbaar omdat de linkeraangrenzende kolom niet altijd is gevuld. Daardoor kan Excel soms stoppen bij
de eerste de beste lege regel.

Om al die onzekerheid weg te nemen zoek ik een formule in mijn macro die een volledige kolom telt en met die uitkomst de formule copieert in een range
bijvoorbeel range(17.000:REGELTELLER)

Ben ik zo een beetje duidelijker?

 

[oeldere]

Dan ga je voorbij aan de opmerking van het maken van een tabel.

Excel 2013 => invoegen => tabel.

Voeg vervolgens een formule in de tabel toe.

Deze wordt toegevoegd aan alle rijen van de tabel.

 

[johhnnyboy]

En als je de laatst gebruikte rij dan bepaald op basis van kolom B?

Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("JOUWSHEETNAAM")


  LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row

Msgbox LastRow 

End sub

't probleem is mij niet duidelijk eerlijk gezegd, maar wellicht helpt bovenstaand je iets... anders toch echt even een vb bestandje

 

[PaulMak]

Jonniiboy,

Jouw oplossing werkt goed!

Nu heb ik het vervolg nog nodig ... een formule uit een cel copieeren naar een range zoals bijvoorbeeld;

stap 1: kopieer de inhoud (de formule) van cel A1
stap 2: selecteer een range ("Z2:LastRow")
stap 3: plak de formule uit A1

Wanneer ik dit laatste stuk heb dan ben ik er!!!

Ik hoop dat je me kunt helpen ...

Groeten
Paul

 

[johhnnyboy]

Antwoord

Zoiets... maar dan zelf even aanpassen naar je project:

Sub aTest()
    
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim strTemp As String
        
    strTemp = Range("A1").Formula
    
    Set sht = ThisWorkbook.Worksheets("Blad1")


  LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
    
   With sht.Range("C1:C" & LastRow)
            .Formula = strTemp
    End With
   
 
End Sub

 

[PaulMak]

Tnxxxxxxxxx

Ik ga 'm morgen op de zaak uitproberen. Ik laat je weten hoe het is gegaan.

Truste
Paul

 

[PaulMak]

Beste johhnnyboy ,

Ik heb de je oplossing toegepast in mijn macro op het werk en ... het werkt !!!

Super bedankt!

Groeten
Paul