CODEWITHSUNDEEP
excelvba
Mani
Mani

Reputation: 35

Macro is not working automatically

I am using a macro to write a datestamp when a column is modified. The idea is that whenever the status changes it gives the running time for that particular status. I have four columns:

A                   b            c     d
clearing           24.04.2015    1    empty
**when stauts is changed**
A                   b            c      d
wait for start     24.04.2015    2     24.04.2015

formual for c is : 

IF(RC[-2]="";"";IF(RC[-2]="clearing";1;2))
Macro;

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Value = "clearing"
  Then
    Cells(Target.Row, 2) = Date
  Else
     If Target.Column = 3 And Target.Value = 2 
     Then
        Cells(Target.Row, 4) = Date
     End If
  End If
End Sub

The problem is when C column is, with the help of formula, changed to 2 the macro does not automatically give me the date, but when I insert that manually it's working.

Upvotes: 2

Views: 425

Answers (1)

user4039065
user4039065

Reputation:

When you put values into the worksheet that is triggering the Worksheet_Change event macro, you should always turn off events or the macro will try to run on top of itself.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        On Error GoTo Fìn
        Application.EnableEvents = False
        Dim rng As Range
        For Each rng In Intersect(Target, Columns(1))
            If LCase(rng.Value) = "clearing" Then
                Cells(rng.Row, 2) = Now
                Cells(rng.Row, 2).NumberFormat = "dd.mm.yyyy"
                'Cells(rng.Row, 3).FormulaR1C1 = "maybe put the formula in here"
            ElseIf rng.Offset(0, 2).Value = 2 Then
                Cells(rng.Row, 4) = Now
                Cells(rng.Row, 4).NumberFormat = "dd.mm.yyyy"
            End If
        Next rng
    End If
Fìn:
    Application.EnableEvents = True
End Sub

It sounds like you already have that formula in column C but I left a place where you can put it in once column A gets the clearing value. Another option would be to simply write a 1 into column C and the next time write a 2 in column C. That way you wouldn't have to deal with the formula at all.

Upvotes: 2

Related Questions