Record time

[hsandeep]

Sheet1

	C	D	E	F	G
1	1 or 0	Time T1 when 1 appeared last in C	Time T2 when 1 disappeared last in C	For T1	For T2
2	1	9/21/2014 11:03	9/21/2014 13:47	100	200
3	1
4	0
5	1
6	0

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 104px"><col style="WIDTH: 104px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

C2:C200 generates either of 1 or 0 using Excel function. C2:C200 gets updated regularly & the value in C2:C200 may change back from 1 to 0 after generating 1.
Say C2=1 when time=9/21/2014 10:44:36 AM. I want to record this time in D2 & a constant numerical value 100 should appear in F2.
After few seconds, C2 may change to 0. Say C2 becomes 0 at time=9/21/2014 11:16:12 AM. I want to record this time in E2 & a constant numerical value 200 should appear in G2.

By default, C2:C200=0.
How to do this in D2:G200 using vba.
Thanks in advance for having helped me.

 

[ttdk1]

you need to put this code in the worksheet you want this to happen.

Private Sub Worksheet_Change(ByVal Target As Range)


  If Not Intersect(Target, Range("C2:C200")) Is Nothing Then
    If Range("C" & Target.Row).Value = 1 Then
      Range("D" & Target.Row).Value = Now()
      Range("F" & Target.Row).Value = 100
    Else
      Range("E" & Target.Row).Value = Now()
      Range("G" & Target.Row).Value = 200
    End If
  End If
End Sub

 

[ztodd]

That would only work if the formula in C is based on a cell in the same row that changes and causes the value in C to change to 0 or 1 (hsandeep said "C2:C200 generates either of 1 or 0 using Excel function- so worksheet_change won't be called for change on column c directly). You'd have to change or take out the outer If statement as well.

 

[ttdk1]

ztodd is correct. the previous macro is only activated when the change is manually inputted not generated.

here's the revised macro. You need to add a tab and label it Temp.
in a module, add this macro to copy data from Sheet3, the sheet where the the table is located at

Sub setup()
  Worksheets("Temp").Range("A2:A200").Value = Worksheets("Sheets").Range("C2:C200").Value
End Sub

in Sheet3, add this macro

Private Sub Worksheet_Calculate()


  Application.EnableEvents = False
  Set Rng = Intersect(Worksheets("Sheet3").UsedRange, Worksheets("Sheet3").Range("C2:C200"))
  If Rng Is Nothing Then GoTo ExitHere
  
  For Each c In Rng
    If c.Value <> Worksheets("Temp").Range("A" & c.Row).Value Then
      Worksheets("Temp").Range("A" & c.Row).Value = c.Value
      If c.Value = 1 Then
        Worksheets("Sheet3").Range("D" & c.Row).Value = Now()
        Worksheets("Sheet3").Range("F" & c.Row).Value = 100
      Else
        Worksheets("Sheet3").Range("E" & c.Row).Value = Now()
        Worksheets("Sheet3").Range("G" & c.Row).Value = 200
      End If
    End If
  Next c
  
ExitHere:
  Application.EnableEvents = True
End Sub

you can put the macro elsewhere but some revisions to the macro is needed

 

[hsandeep]

you can put the macro elsewhere but some revisions to the macro is needed[/QUOTE]

I think the code WORKS. I'll test it tomorrow with large data & that too being changing from 1 to 0 to 1 to 0 .......& would give you the outcome.

Can you explain what you mean by "you can put the macro elsewhere but some revisions to the macro is needed"?

Kindly note again: the value in C is generated by formula & NOT MANUALLY PUNCHED. Is the macro suited for 'generation' of 1 or 0 or not?

 

[ttdk1]

the new code has been tested to work for generation of values in C, the macro must be run on Sheets3. If you want to do it on another worksheet, places where the macro references Sheet3 needs to be changed.

 

[hsandeep]

Okay got it. Thanks bro.... I'll revert with the outcome by tomorrow/day after....

 

[hsandeep]

ttdk1,

My Excel workbook has more than 20 ws & some of the ws contains macros. I'll insert 2 more ws tab named "Sheet3" (code in WORKSHEET) & "Temp" (CODE in MODULE). My question is: Having other ws also in the SAME Excel workbook, will the code run or not?

 

[ttdk1]

yes, it will run…

 

[hsandeep]

Okay.