Record time

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
Sheet1

CDEFG
11 or 0Time T1 when 1 appeared last in CTime T2 when 1 disappeared last in CFor T1For T2
219/21/2014 11:039/21/2014 13:47100200
31
40
51
60

<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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
you need to put this code in the worksheet you want this to happen.

Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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

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

in Sheet3, add this macro

Code:
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
Okay got it. Thanks bro.... I'll revert with the outcome by tomorrow/day after....
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top