Automatisch formule in volgende regel

[toverkamp]

Hallo forumleden,

Ik heb een bestand gemaakt waar in een werkblad een aantal formules staan. Deze heb ik doorgetrokken tot rij 3000, maar dit lijkt me een beetje overbodig, en daardoor wordt het bestand volgens mij overbodig groot en traag.
Is het dus mogelijk als de datum in kolom B is ingevuld dat de formules uit die rij, automatisch wordt doorgetrokken naar de volgende rij.

Vb.
In cel B3 is de datum ingevoerd en in kolom M3 & N3 staan formules. Cel B3 is nu gevuld dus nu moeten in kolom M4 en N4 de formule uit rij 3 worden doorgetrokken.

Zie de bijlage voor een klein voorbeeldbestand.

 

[Wigi]

Waarom geen Worksheet_Change event?

 

[toverkamp]

Waarom geen Worksheet_Change event?

wat houdt een Worksheet_Change event precies in? Ik heb het volgende gevonden in de help-file:

Private Sub Worksheet_Change(ByVal Target as Range)
    Target.Font.ColorIndex = 5
End Sub

maar hoe komt dat er ongeveer uit te zien in mijn geval? Misschien kun je een opzetje geven?

 

[Wigi]

Er wordt code uitgevoerd vanaf een cel wijzigt in een werkblad.

Hier op het forum en in de helpfiles zijn massa's voorbeelden te vinden.

 

[Demeter]

Je kan toch ook na het invullen van je gegevens, volgen mij doe jij dit met een userform, deze cellen een format meegeven? Je hebt dan toch ook al het regelnummer van deze rij. en met een offset het regelnummer van de onderliggende rij .

Groet,
Ferenc

ps.
Voor een worksheet event moet je wel een werkblad in je vb-editor hebben geselecteerd en niet een module.
Kies dan in het pulldown menu waar algemeen staat worksheet en in de pulldown ernaast voor Change.

 

[toverkamp]

Je kan toch ook na het invullen van je gegevens, volgen mij doe jij dit met een userform, deze cellen een format meegeven? Je hebt dan toch ook al het regelnummer van deze rij. en met een offset het regelnummer van de onderliggende rij .

Groet,
Ferenc

Ik vul de gegevens inderdaad in met een userform. Het regelnummer wordt bepaald door de code.

legeregel = Worksheets("Reactietijden").Range("B" & Rows.Count).End(xlUp).Row + 1

De gegevens worden in de eerstvolgende lege rij geplaatst. Maar ik snap nog steeds niet hoe ik de formule's in het werkblad als het ware kan doortrekken naar de eerstvolgende regel.

Ik heb de volgende code gevonden in dit topic http://www.helpmij.nl/forum/showthread.php?t=260182&highlight=worksheet_change
Maar waar moet ik deze code plaatsen, in thisworkbook?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
    With Target
        If .Column = 2 And .Row > 1 And .Count = 1 Then
            If Cells(.Row - 1, 1).HasFormula Then Cells(.Row - 1, 1).copy Cells(.Row - 1, 1)
            For col = 3 To 9
                If Cells(.Row - 1, col).HasFormula Then Cells(.Row - 1, col).copy Cells(.Row, col)
            Next
        End If
    End With
End Sub

 

[Demeter]

Toverkamp,

kijk eens naar de functie Datediff in de vba help?
Ooit een tip van Spiderman, waarvoor dank.

Zie attachment voor voorbeeld.

Groet,
Ferenc

 

[toverkamp]

demeter,

dat is volgens mij niet helemaal wat ik bedoel. de code die jij hier gebruikt:

Range("G1") = DateDiff("h", Range("A1"), Range("D1"))
Range("G2") = DateDiff("d", Range("A1"), Range("D1"))
Range("G3") = DateDiff("m", Range("A1"), Range("D1"))

daar staan alle cellen vast. Ik zou dus graag zien dat als bijvoorbeeld cel B3 is gevuld. Dat alle codes die in rij 3 staan, gekopieerd worden naar rij 4.

 

[Wigi]

Maar waar moet ik deze code plaatsen, in thisworkbook?

Uiteraard achter het blad waarvoor het moet gelden :shocked:

 

[Demeter]

mmmm,
voor kolom L (het is L en M niet M en N):

legeregel = Worksheets("Reactietijden").Range("B" & Rows.Count).End(xlUp).Row + 1
Range("L" & legeregel) = DateDiff("d", Range("A" & legeregel), Range("F" & legeregel))

 

[Wigi]

mmmm,
voor kolom L (het is L en M niet M en N):

legeregel = Worksheets("Reactietijden").Range("B" & Rows.Count).End(xlUp).Row + 1
Range("L" & legeregel) = DateDiff("d", Range("A" & legeregel), Range("F" & legeregel))

Dit zet de uitkomst van de berekening, niet de formule zelf.

 

[toverkamp]

Ik ben eruit gekomen. Met de volgende code word als een cel in kolom 2 (B) gevuld is dan worden in de eerste 54 kolommen de formules doorgetrokken:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Integer
    With Target
        If .Column = 2 And .Row > 1 And .Count = 1 Then
            If Cells(.Row - 1, 1).HasFormula Then Cells(.Row - 1, 1).copy Cells(.Row - 1, 1)
            For col = 3 To 54
                If Cells(.Row - 1, col).HasFormula Then Cells(.Row - 1, col).copy Cells(.Row, col)
            Next
        End If
    End With
End Sub

 

[Demeter]

Waarom wil je je formules door trekken?
er staan nergens gegevens welke je nodig hebt voor je berekening(en).

Groet,
Ferenc

ps.
post #9
volgens mij komt die quote niet bij mij vandaan!!!

 

[toverkamp]

de bijlage was maar een klein voorbeeldbestandje. In het orgineel staan vele berekeningen. Er worden storingen gemeld en aan de hand daarvan wordt de reactietijd uitgerekend. Wanneer ik al die formules in de cellen zou doortrekken tot bijvoorbeeld regel 3000 wordt het bestand onnodig groot en traag. Tevens bestaat de kans dat de 3000 rijen een keer gepasseerd wordt, en moet je de formules weer doortrekken. D.m.v. deze code heb je als het ware een variabele range en hoef je je geen zorgen te maken dat de formule doorgetrokken moet worden.

 

[Wigi]

Groet,
Ferenc

ps.
post #9
volgens mij komt die quote niet bij mij vandaan!!!

Klopt. Oorzaak was het slechte "quoten" van Toverkamp.

 

[Demeter]

Heren,

Het is mij volledig duidelijk.
Thanks voor je code Toverkamp staat bij mijn verzameling.:thumb:

Groet,
Ferenc

 

[toverkamp]

Ik had toch nog een vraagje over dit probleem, want ik heb ook een werkblad met verwijzingen naar een ander werkblad. Ook in dit werkblad heb ik de formules doorgetrokken naar rij 3000.
De codes die in dit werkblad staan zien er allemaal ongeveer zo uit (alleen de kolom verschilt elke keer):

=ALS(Reactietijden!AP5="Nee";Reactietijden!B5;"")

Dus als in het werkblad "reactietijden" cel AP5 een "nee" staat dan moet die cel in de cel worden geplaatst waar deze code staat. Maar ik kan hier dus niet de code gebruiken die ik eerder in dit topic heb geplaatst gebruiken, omdat er lang niet altijd een "nee" staat.

Heeft misschien iemand een idee hoe ik dit kan oplossen?
alvast bedankt!:thumb:

 

[Demeter]

Als er geen nee staat zou je een - teken kunnen laten plaatsen. dan is je cel ook niet leeg en pakt hij automatisch de volgenden?

Of wil je geen - teken in je cel? dan zou je deze met VB de achtergrond kleur mee kunnen laten geven.

Groet,
Ferenc

 

[toverkamp]

Als er geen nee staat zou je een - teken kunnen laten plaatsen. dan is je cel ook niet leeg en pakt hij automatisch de volgenden?

Of wil je geen - teken in je cel? dan zou je deze met VB de achtergrond kleur mee kunnen laten geven.

Groet,
Ferenc

Demeter, dat zou inderdaad een oplossing kunnen zijn, maar ik heb in dat werkblad ook een autofilter staat die automatisch selecteerd op niet-lege cellen. Dus als daar een - teken zou staan, dan filtert hij die er niet uit en zie je overal nog wit-regels tussen...