In Excel een formule in een cel zetten via VBA.

[RuudRutten]

Ik heb in Excel 3 kolommen (A,B,C).

In Kolom A staat een datum waarop een periodieke inspectie uitgevoerd moet worden.
In Kolom B staat de datum van de laatste keer dat deze inspectie uitgevoerd is.
In Kolom C staat een interval dat aangeeft om de hoeveel weken de inspectie uitgevoerd moet worden.

Nu laat ik de datum in A berekenen door de datum in B en het interval in C.
Het probleem is dat ik nu ook datums wil toevoegen die geen interval hebben. A moet dan geen formule bevatte. Ik wil in dat geval dus handmatig een datum invoeren.


De code die ik in VBA gemaakt heb is als volgt:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = “$C$1$” Then
Range(“A1”).ClearContents
If Range(“C1”) <> “” Then Range(“A1”).Formula = “=B1+C1*7”
End If
End Sub


Deze code kijkt dus of C1 gevuld is, als dit het geval is dan wordt A1 gevuld met de formule “=B1+C1*7”. Is C1 leeg, dan is A1 ook leeg, zodat er handmatig een datum ingevoerd kan worden.

Deze code werkt nu alleen voor rij 1.
Ik wil graag dat deze code voor de hele kolom werkt (of t/m bijv. rij 100).
Ik zat te denken aan een loop, maar ik kom er niet uit.

Kan iemand mij helpen?

 

[Elsendoorn2134]

Ruud,

De code die je nodig hebt is deze:

Private Sub Worksheet_Change(ByVal Target As Range)

If Left(Target.Address, 2) = "$C" Then
    With Range(Target.Address)
        .Offset(0, -2).Value = ""
        If .Value <> "" Then .Offset(0, -2).Formula = "=" & .Offset(0, -1).Address & "+" & .Address & "*7"
    End With
End If

End Sub

 

[snb]

@elsen...

Target is de eenvoudigste manier
Range(Target.Address) is beetje teveeel van het goede voor hetzelfde resultaat.

 

[RuudRutten]

@elsendoorn2134

Code werkt goed, bedankt.
Ik kan echter niet de kolom C doortrekken, ik moet dus voor elke cel apart het interval invullen.

@snb
Hoe zou de eenvoudigere code er precies uitzien?

En hoe zorg je ervoor dat je in de formule relatieve celverwijzingen krijgt i.p.v. absolute?

Mvg,
Ruud

 

[snb]

Gebruik geen gebeurtenisprocedure:

Sub snb()
  for each cl in columns(3).specialcells(2)
    cl.offset(,-2)=dateadd("ww",cl.cl.offset(,-1))
  next
end sub

 

[Warme bakkertje]

Kleine correctie

Sub snb()
  For Each cl In Columns(3).SpecialCells(2)
    cl.Offset(, -2) = DateAdd("ww", cl[COLOR="#FF0000"],[/COLOR] cl.Offset(, -1))
  Next
End Sub

 

[snb]

@wb

Maar wel essentieel....

 

[RuudRutten]

De formule die ik in de cel wil zetten is toch langer dan in het voorbeeld. Namelijk met een als-functie erin.

Ik heb daarom de formule gebruikt van elsendoorn. Ik dacht dat alles goed werkte, maar nu loop ik toch tegen een probleempje aan.
De formule die in de cel gezet wordt, bevat absolute celverwijzingen. Ik wil hier graag relatieve verwijzingen van maken. (Anders gaat het fout als ik de datums sorteer van oud naar nieuw)


De code zoals hij nu is:

Private Sub Worksheet_Change(ByVal Target As Range)

If Left(Target.Address, 2) = "$C" Then
    With Range(Target.Address)
        .Offset(0, -2).Value = ""
        If .Value <> "" Then .Offset(0, -2).Formula = "=" & .Offset(0, -1).Address & "+" & .Address & "*7"
    End With
End If

End Sub

Dit geeft in cel A1 de formule: =$B$1+$C$1*7
Ik wil graag de formule: =$B1+$C1*7 of =B1+C1*7

Weet iemand hoe ik dit voor elkaar krijg?

Mvg,
Ruud

 

[snb]

Door eerst code tags in je bericht te zetten (zie de forum regels)

 

[RuudRutten]

@snb

Bericht is aangepast,
mijn excuses.

Mvg,
Ruud

 

[Warme bakkertje]

If .Value <> "" Then .Offset(0, -2).Formula = "=RC[+1]+RC[+2]*7"

 

[RuudRutten]

Het werkt perfect,
bedankt.

Mvg,
Ruud